Run VBA code in a different workbook manually

Question

I created VBA code in one workbook.

Every month I am sent a new sheet from which I calculate the leaves of the current month. The structure is same.

How do I run the code in another workbook?

This is my code in entirety-

Public Sub CopyAndHideDataForJanuary()
Call CopyDataFromMultipleSheets
Call HideNonSaturdayColumns
End Sub

Sub CopyDataFromMultipleSheets()
Dim ws As Worksheet
Dim newWs As Worksheet
Dim lastRow As Long
Dim startDate As Date
Dim endDate As Date
Dim currentDate As Date
Dim currentCol As Long
Dim monthName As String

'Calculate start and end dates for current month
startDate = DateSerial(Year(Date), Month(Date), 26)
endDate = DateSerial(Year(Date), Month(Date) + 1, 25)

'Create a new worksheet named "All Data"
 monthName = Format(Date, "mmm")
 Set newWs = ThisWorkbook.Worksheets.Add(After:= _
         ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
 newWs.Name = monthName & "_All_Data"

'Copy headers from Sheet1
 ThisWorkbook.Worksheets("Sheet1").Range("C5:AG5").Copy 
newWs.Range("C1")

'Format headers
newWs.Range("A1").Value = "Employee Code"
newWs.Range("A1").EntireColumn.ColumnWidth = 15
newWs.Range("B1").Value = "Name"
newWs.Range("B1").EntireColumn.ColumnWidth = 25
newWs.Range("AJ1").Value = "Leaves Taken"
newWs.Range("AK1").Value = "Carry Forward"
newWs.Range("AL1").Value = "Balance Leaves Feb"

Set formulaRange = newWs.Range("AJ2:AJ500")
formulaRange.Formula = 
"=IF(A2="""","""",SUMPRODUCT(((WEEKDAY($C$1:$AG$1)=7)*. ((C2:AG2=""L(CL)"")+(C2:AG2=""A"")+(C2:AG2=""WO"")))))"
Set formulaRange = newWs.Range("AK2:AK500")
formulaRange.Formula = "=AJ2-2"

'Format date columns
currentCol = 3
currentDate = startDate
While currentDate <= endDate
    newWs.Cells(1, currentCol).Value = currentDate
    newWs.Cells(1, currentCol).NumberFormat = "dd/mm/yyyy"
    currentDate = currentDate + 1
    currentCol = currentCol + 1
Wend

'Copy data from each sheet named like "Sheet1", "Sheet2", etc.
For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 5) = "Sheet" Then
        lastRow = newWs.Cells(Rows.Count, 1).End(xlUp).Row
        newWs.Range("A" & lastRow + 1).Value = 
ws.Range("F3").Value
        newWs.Range("B" & lastRow + 1).Value = 
ws.Range("O3").Value
        newWs.Range("C" & lastRow + 1 & ":AG" & lastRow + 1). 
Value = ws.Range("C14:AG14").Value
    End If
Next ws

'Update date range for next month
startDate = DateSerial(Year(Date), Month(Date) - 1, 26)
endDate = DateSerial(Year(Date), Month(Date), 25)
currentCol = 3
currentDate = startDate
While currentDate <= endDate
    newWs.Cells(1, currentCol).Value = currentDate
    newWs.Cells(1, currentCol).NumberFormat = "ddd, mmm dd"
    currentDate = currentDate + 1
    currentCol = currentCol + 1
Wend

With newWs.Range("A1:AZ1")
   .Font.Size = 11 ' Increase font size to 11
   .Font.Bold = True ' Make headers bold
End With
newWs.Columns("AJ").ColumnWidth = 15
newWs.Columns("AK").ColumnWidth = 19
newWs.Columns("AL").ColumnWidth = 19
End Sub


Sub HideNonSaturdayColumns()
Dim ws As Worksheet
Dim rng As Range
Dim lastCol As Long
Dim monthName As String

monthName = Format(Date, "mmm") 'Get current month name

'Loop through all worksheets
For Each ws In ThisWorkbook.Worksheets
    If InStr(1, ws.Name, monthName) > 0 Then 'Only hide columns 
in sheets containing the current month name
        'Find the last column in row 1
        lastCol = ws.Cells(1, 
        ws.Columns.Count).End(xlToLeft).Column
        
        'Loop through all columns in row 1
        For Each rng In ws.Range(ws.Cells(1, 3), ws.Cells(1, lastCol))
            'Hide the column if it's not a Saturday
            If IsDate(rng.Value) And Weekday(rng.Value) <> 7 Then
                rng.EntireColumn.Hidden = True
            Else
                rng.EntireColumn.Hidden = False
            End If
        Next rng
    End If
    Next ws
End Sub

The code works. I just have to figure out a way to make it run in every new sheet I get.

Answer 1

Just store your code in a "master" workbook, and explicitly refer to the new workbook. Here is a very much simplified example

Option Explicit

Sub Demo()

    Dim newWB As Workbook
    Set newWB = Workbooks.Open("c:\temp\demo.xlsx") 'parameterise this bit
    
    Dim newWS As Worksheet
    Set newWS = newWB.Sheets(1)     'Using index rather than name in case it changes each month
    
    Dim source As Variant
    source = newWS.Range("A1:Z1000").Value
    'Source is now an array which you can process
    
    Dim srcRange As Range, targetRange As Range
    Set srcRange = newWS.Range("A1:Z1000")
    Set targetRange = ThisWorkbook.Sheets(2).Range("A1:Z1000")
    targetRange.Value = srcRange.Value
    
    newWB.Close SaveChanges:=False

End Sub

So this demo opens another workbook and refers to a range in that workbook. It demonstrates a way of getting the data into memory (source), or copying the data into the current workbook if you prefer. Either way, you now have access to the new workbook but your code is in a different workbook