Selecting a specific sheet by codename in an opened workbook (XL03

[ker_01]

I have a master data workbook, and will be pulling in data from source XL
files from various locations.

At the moment, I'm trying to create a function that will allow me to pass a
location (workbook to pull), and specify which sheet to grab from the source
workbook, and which sheet in the master data workbook to past that sheet into.

I'm having some trouble (error: does not support this property or method)
specifying the source sheet in the newly opened source workbook- can anyone
give me some pointers? (I replaced path/filename to shorten the code line
below in the test sub, but the path is valid and it does open the correct
file)

Thank you!!
Keith

Sub test
zz= PullAllRawData("C:\path\filename.xlsx",Sheet1,Sheet13)
end sub

Function PullAllRawData(MyFullFilePath As String, _
SourceSheet As Worksheet, _
DestSheet As Worksheet)

Dim i As Integer
Dim owb As Workbook 'original/main
Dim twb As Workbook 'temp/data file
Dim ows As Worksheet
Dim tws As Worksheet

DestSheet.Activate
Set owb = ActiveWorkbook
Set ows = ActiveWorkbook.ActiveSheet
'clear the destination sheet to make sure there isn't leftover old data
ows.Cells.Clear

Application.StatusBar = "Opening File " & MyFullFilePath

'Open source workbook
Set twb = Workbooks.Open(FileName:=MyFullFilePath)
twb.Activate
twb.SourceSheet.Activate '<<<< this is where it errors out
'grab the data
twb.SourceSheet.Cells.Select
Selection.Copy
ows.Activate
ows.Range("A1").Select
ActiveSheet.Paste

'Select/copy a single cell to avoid clipboard warnings
ActiveSheet.Range("A1").Copy

'close the workbook to get it out of the way
Application.DisplayAlerts = False 'just in case the clipboard trick doesn't
work
twb.Close SaveChanges:=False
Application.DisplayAlerts = True

End Function

 

[John Bundy]

Try setting the sheet name as a string and then call it like this

mysheet = "Sheet3"
Sheets(mysheet).Select

 

[ker_01]

Hi John-

I'll give that a try, but I suspect that this method uses the "user" name of
the sheet, rather than the "code" sheetname. The source workbook is coming
from another department, and I won't have an control over whether they
reorder the sheets (e.g. change the index number) or rename the sheet through
the GUI- I was hoping to use just the code sheetname to avoid either scenario.

I'll give it a shot, and we'll see what happens.

Thanks,
Keith