Error Copying Worksheet
 

My application requires me to copy a worksheet from the current workbook to a
new workbook and save it in a filenaming convention. I get a path, worksheet
name to construct a filename and then create and save the workbook. This
seems relatively easy but when I attempt to copy the worksheet to the new
workbook I get a subscript error.

ListWorksheets is a Listbox containing the worksheets names and filenames
that will be used for the workbook. If the user selects a worksheet to be
copied to the workbook and saved the following code is run. The error occurs
on the .Copy statement. I've attempted several types of the copy statement
as shown in the commented line below it. During debugging I trap at the
statement and am able to copy oWb.Sheets(1) into the immediate window as
oWb.Sheets(1).Name and return the sheet name 'Sheet1' so I know the
oWb.Sheets(1).Name is valid.

For Item = 0 To Me.ListWorksheets.ListCount - 1
If Me.ListWorksheets.Selected(Item) Then
strWsName = Me.ListWorksheets.Column(0, Item)
Set oWb = Workbooks.Add
oWb.SaveAs Filename:=strFilename
Sheets(strWsName).Copy Befo=oWb.Sheets(1)
'ActiveWorkbook.Worksheets(strWsName).Copy
Befo=Workbooks(oWb.Name).Sheets(1)
End If

Research on the internet and the Microsoft MSDN and Knowledgebase have
proven fruitless. Any assistance in this matter would be appreciated.

--
Steve King

This line:
Sheets(strWsName).Copy Befo=oWb.Sheets(1)

is pointing to the activeworkbook for sheets(strwsname). Since you just added a
new workbook, that should be the activeworkbook. I bet it doesn't have a
worksheet by that name.

maybe something like this would work:

dim ActWkbk as workbook
set actwkbk = activeworkbook

For Item = 0 To Me.ListWorksheets.ListCount - 1
If Me.ListWorksheets.Selected(Item) Then
strWsName = Me.ListWorksheets.Column(0, Item)
Set oWb = Workbooks.Add
oWb.SaveAs Filename:=strFilename
actwkbk.Sheets(strWsName).Copy Befo=oWb.Sheets(1)
End If
next item

Steve King wrote:

My application requires me to copy a worksheet from the current workbook to a
new workbook and save it in a filenaming convention. I get a path, worksheet
name to construct a filename and then create and save the workbook. This
seems relatively easy but when I attempt to copy the worksheet to the new
workbook I get a subscript error.

ListWorksheets is a Listbox containing the worksheets names and filenames
that will be used for the workbook. If the user selects a worksheet to be
copied to the workbook and saved the following code is run. The error occurs
on the .Copy statement. I've attempted several types of the copy statement
as shown in the commented line below it. During debugging I trap at the
statement and am able to copy oWb.Sheets(1) into the immediate window as
oWb.Sheets(1).Name and return the sheet name 'Sheet1' so I know the
oWb.Sheets(1).Name is valid.

For Item = 0 To Me.ListWorksheets.ListCount - 1
If Me.ListWorksheets.Selected(Item) Then
strWsName = Me.ListWorksheets.Column(0, Item)
Set oWb = Workbooks.Add
oWb.SaveAs Filename:=strFilename
Sheets(strWsName).Copy Befo=oWb.Sheets(1)
'ActiveWorkbook.Worksheets(strWsName).Copy
Befo=Workbooks(oWb.Name).Sheets(1)
End If

Research on the internet and the Microsoft MSDN and Knowledgebase have
proven fruitless. Any assistance in this matter would be appreciated.

--
Steve King

--

Dave Peterson