copy worksheet from inactive workbook

[Guest]

I want to copy an entire worksheet from an inactive workbook without opening
it. Would someone be so kind as to talk me through this? For more
background, I am having the user enter a list of file names; I want to
automatically copy a certain worksheet (same name each workbook) from each of
the listed workbooks into the active workbook. I have code working that
copies the names of the listed workbooks into a string array (not sure if
that was the way to go or not).

 

[Guest]

You're going to need to open the workbook in order to copy the worksheet.
You could open it as READ ONLY.

You could try something liket this:

Dim oWB As Workbook
Dim oWS As Worksheet
Dim aWB As Workbook
Set aWB = activebook

Dim Filename As String
Filename = aws.Cells(1, 1).Value '<~~~ change this It should be something
like C:\Documents and Settings\Customer\My Documents\filename.xls
Set oWB = Workbooks.Open(Filename, UpdateLinks:=False, ReadOnly:=True)
On Error Resume Next
Set oWS = oWB.Worksheet("Sheet1") '<~~~~CHANGE THE SHEET NAME HERE
On Error GoTo 0
If Not oWS Is Nothing Then
oWS.Copy AFTER:=aWB.Sheets(aWB.Worksheets.Count)
End If