Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 13th 05, 03:42 PM
Steve King
 
Posts: n/a
Default 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

  #2   Report Post  
Old April 13th 05, 09:49 PM
Dave Peterson
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search/Match between 2 x separate Worksheets and populate result in third worksheet Alan Bartley Excel Discussion (Misc queries) 1 April 11th 05 05:21 AM
Copy from worksheet to another x times Union70 Excel Discussion (Misc queries) 0 March 7th 05 10:03 PM
Weekly Transaction Processing Ralph Howarth Excel Worksheet Functions 4 January 19th 05 06:37 AM
Indirect reference from one worksheet to another Bill Sturdevant Excel Worksheet Functions 2 December 17th 04 02:23 PM
Reference Data in Moved Worksheet tommcbrny Setting up and Configuration of Excel 1 December 1st 04 07:49 PM


All times are GMT +1. The time now is 05:06 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017