Passing worksheet variable to a sub

[Gronwold]

Hi all,
I would like to pass a worksheet variable to a sub as follows:

sub InsertData()
Dim wsDetails2 As Worksheet

 CopySheetsIntoVariable "Details (Routine 2)", wsDetails2

MsgBox wsDetails2.Cells(1, 3)

end sub

Sub CopySheetsIntoVariable(sName As String, ByRef ws As Worksheet)
    
    Dim wbTemp As Workbook
    Dim sSaveLocation As String
    
    'This routine extracts the file save location from the registry
    sSaveLocation = GetSetting("SaveLocation")
    
    'This copies the closed workbooks into the variable
    If Dir(sSaveLocation & sName & ".xlsx", vbDirectory) = "" Then
        MsgBox "Error - Cannot find the file '" & sName & ".xlsx'.  Exiting.", , "File not found"
        Else
        Set wbTemp = Workbooks.Open(sSaveLocation & sName & ".xlsx")
        Set ws = wbTemp.Sheets("Data")
        wbTemp.Close
        End If
        
End Sub

However, all I get is an 'Automation Error' when I get to the line 'MsgBox wsStudentDetails2.Cells(1, 3)'
Would really appreciate it if anyone could suggest why!
Many thanks!1947

 

[Kyle123]

Your sub closes the workbook...

Just remove wbtemp.close

I think you're misunderstanding how object variables work, nothing is copied anywhere, you are just passing a pointer around to the same object

 

[Gronwold]

Thank you Kyle123 - no I hadn't realised that - I thought that I had created two variables and copied one to the other, allowing me to close the first.
OK - I closed the workbooks as they open fully on the screen, which I don't want. I could use screenupdating=false, but would prefer either:
copying the contents of the worksheet into a variable so I can close the workbook opened, or
opening the worksheets in memory only, so they are not visible in excel. How could I go about that?
Cheers!
2011