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).

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




"br549" wrote:

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).