Carryover variable to called sub

[dlowell09]

I am attempting to simplify some VBA code as it has gotten fairly long and is taking an extensive amount of time to run. I have multiple cubes that are updated weekly and I need to copy data from those cubes to a new sheet where I can manipulate and make edits. Rather than having three subs with identical code, I thought I could set up a separate sub that is called by each of the main subs. The problem is that I have variables to refer to the different sheets and need those to carry down from the primary sub to the secondary sub. The problem is that the variable I set in my primary sub is not being recognized by the secondary sub. I just get a runtime error 424 object required.

An example (condensed to show relevant sections):

Sub Update_Actuals_Expansion()

Dim WSCube As Worksheet
Dim WSHist As Worksheet

Set WSCube = ActiveWorkbook.Worksheets(Sheet3)
Set WSHist = ActiveWorkbook.Worksheets(Sheet6)

Call CopyActuals

End Sub

Sub CopyActuals()

'Clear the old historical data to avoid any contamination
    WSHist.Activate
    WSHist.Cells.ClearContents
    
    'Copy new actuals from the cube and paste into the newly cleared historical data sheet
    WSCube.Activate
    WSCube.Range("A8").Select
    ActiveCell.CurrentRegion.Select
    Selection.Copy
    WSHist.Activate
    WSHist.Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
End Sub

 

[dlowell09]

Correction, I copied the old code, variable definition should be this:

Set WSCube = Sheet4
Set WSHist = Sheet5

 

[RoryA]

Pass them as arguments:

Sub CopyActuals(wsHist as Worksheet, wsCube As Worksheet)

'Clear the old historical data to avoid any contamination
    WSHist.Cells.ClearContents
    
    'Copy new actuals from the cube and paste into the newly cleared historical data sheet
    WSCube.Range("A8").CurrentRegion.Copy
    WSHist.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                     SkipBlanks:=False, Transpose:=False
        
End Sub

called using:

Sub Update_Actuals_Expansion()

Dim WSCube As Worksheet
Dim WSHist As Worksheet

Set WSCube = Sheet4
Set WSHist = Sheet5

Call CopyActuals(WSHist, WSCube)

End Sub

 

[dlowell09]

Pass them as arguments:

Sub CopyActuals(wsHist as Worksheet, wsCube As Worksheet)

'Clear the old historical data to avoid any contamination
    WSHist.Cells.ClearContents
   
    'Copy new actuals from the cube and paste into the newly cleared historical data sheet
    WSCube.Range("A8").CurrentRegion.Copy
    WSHist.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                     SkipBlanks:=False, Transpose:=False
       
End Sub

called using:

Sub Update_Actuals_Expansion()

Dim WSCube As Worksheet
Dim WSHist As Worksheet

Set WSCube = Sheet4
Set WSHist = Sheet5

Call CopyActuals(WSHist, WSCube)

End Sub

That is exactly what I needed. Thank you!