Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vba for selecting multiple sheets to save as a new book
I searched through postings but couldn't find one which I could comprehend to
address this issue. Earlier today, your support corrected my code in order to copy&save a single sheet to a new workbook. :) thankyou! Now I have several sheets from the book (over 50 sheets within the book) that we want to copy to a new book. Once again I tried to record the macro, then edit...but I receive a "compile error Sub or Function not defined" The following are the different ways we tried ; Compile errors: Selection([Sheet4], [Sheet5], [Sheet6]).Activate Worksheets(Array(Sheet4, Sheet5, Sheet6)).Activate Wrong set of Argument errors: Selection(Sheet4, Sheet5, Sheet6).Activate Sheets(Sheet4, Sheet5, Sheet6).Activate |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vba for selecting multiple sheets to save as a new book
How about treating those names as text and putting them in double quotes:
Worksheets(Array("sheet4", "Sheet5", "Sheet6")).select (not .activate) If I were you, I'd drop the use of []'s. Sharon wrote: I searched through postings but couldn't find one which I could comprehend to address this issue. Earlier today, your support corrected my code in order to copy&save a single sheet to a new workbook. :) thankyou! Now I have several sheets from the book (over 50 sheets within the book) that we want to copy to a new book. Once again I tried to record the macro, then edit...but I receive a "compile error Sub or Function not defined" The following are the different ways we tried ; Compile errors: Selection([Sheet4], [Sheet5], [Sheet6]).Activate Worksheets(Array(Sheet4, Sheet5, Sheet6)).Activate Wrong set of Argument errors: Selection(Sheet4, Sheet5, Sheet6).Activate Sheets(Sheet4, Sheet5, Sheet6).Activate -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vba for selecting multiple sheets to save as a new book
The tabs actually have text names, sheet4-6 refer to the sheet name when
viewing the sheet in the vba editor. I wanted to use that in case we change a tab's name in the worksheet. I've tried your suggestion both in & without quotes,however, debug advises a "Type mismatch (Error 13) "Dave Peterson" wrote: How about treating those names as text and putting them in double quotes: Worksheets(Array("sheet4", "Sheet5", "Sheet6")).select (not .activate) If I were you, I'd drop the use of []'s. Sharon wrote: I searched through postings but couldn't find one which I could comprehend to address this issue. Earlier today, your support corrected my code in order to copy&save a single sheet to a new workbook. :) thankyou! Now I have several sheets from the book (over 50 sheets within the book) that we want to copy to a new book. Once again I tried to record the macro, then edit...but I receive a "compile error Sub or Function not defined" The following are the different ways we tried ; Compile errors: Selection([Sheet4], [Sheet5], [Sheet6]).Activate Worksheets(Array(Sheet4, Sheet5, Sheet6)).Activate Wrong set of Argument errors: Selection(Sheet4, Sheet5, Sheet6).Activate Sheets(Sheet4, Sheet5, Sheet6).Activate -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vba for selecting multiple sheets to save as a new book
After playing around some more, I found the following syntax works. Thanks
for your time! Sheets(Array(4, 5, 6)).Select Sheets(Array(4, 5, 6)).Copy "Sharon" wrote: The tabs actually have text names, sheet4-6 refer to the sheet name when viewing the sheet in the vba editor. I wanted to use that in case we change a tab's name in the worksheet. I've tried your suggestion both in & without quotes,however, debug advises a "Type mismatch (Error 13) "Dave Peterson" wrote: How about treating those names as text and putting them in double quotes: Worksheets(Array("sheet4", "Sheet5", "Sheet6")).select (not .activate) If I were you, I'd drop the use of []'s. Sharon wrote: I searched through postings but couldn't find one which I could comprehend to address this issue. Earlier today, your support corrected my code in order to copy&save a single sheet to a new workbook. :) thankyou! Now I have several sheets from the book (over 50 sheets within the book) that we want to copy to a new book. Once again I tried to record the macro, then edit...but I receive a "compile error Sub or Function not defined" The following are the different ways we tried ; Compile errors: Selection([Sheet4], [Sheet5], [Sheet6]).Activate Worksheets(Array(Sheet4, Sheet5, Sheet6)).Activate Wrong set of Argument errors: Selection(Sheet4, Sheet5, Sheet6).Activate Sheets(Sheet4, Sheet5, Sheet6).Activate -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vba for selecting multiple sheets to save as a new book
If you're afraid of worksheet names changing, aren't you concerned that the
sheets will be moved (left or right)??? Maybe using the codenames to get the sheet names would be safer. Option Explicit Sub testme02() Dim myCodeNames As Variant Dim iCtr As Long Dim mySheetNames() As String Dim mySht As Object Dim res As Variant With ActiveWorkbook myCodeNames = Array("Sheet1", "sheet3", "Sheet5") ReDim mySheetNames(1 To .Sheets.Count) iCtr = 0 For Each mySht In .Sheets res = Application.Match(mySht.CodeName, myCodeNames, 0) If IsError(res) Then 'no match, the board goes back Else iCtr = iCtr + 1 mySheetNames(iCtr) = mySht.Name End If Next mySht If iCtr = 0 Then MsgBox "no sheets to copy!" Else If UBound(myCodeNames) - LBound(myCodeNames) + 1 < iCtr Then MsgBox "Not all sheets found" Else ReDim Preserve mySheetNames(1 To iCtr) .Sheets(mySheetNames).Copy 'to a new workbook? End If End If End With End Sub Sharon wrote: After playing around some more, I found the following syntax works. Thanks for your time! Sheets(Array(4, 5, 6)).Select Sheets(Array(4, 5, 6)).Copy "Sharon" wrote: The tabs actually have text names, sheet4-6 refer to the sheet name when viewing the sheet in the vba editor. I wanted to use that in case we change a tab's name in the worksheet. I've tried your suggestion both in & without quotes,however, debug advises a "Type mismatch (Error 13) "Dave Peterson" wrote: How about treating those names as text and putting them in double quotes: Worksheets(Array("sheet4", "Sheet5", "Sheet6")).select (not .activate) If I were you, I'd drop the use of []'s. Sharon wrote: I searched through postings but couldn't find one which I could comprehend to address this issue. Earlier today, your support corrected my code in order to copy&save a single sheet to a new workbook. :) thankyou! Now I have several sheets from the book (over 50 sheets within the book) that we want to copy to a new book. Once again I tried to record the macro, then edit...but I receive a "compile error Sub or Function not defined" The following are the different ways we tried ; Compile errors: Selection([Sheet4], [Sheet5], [Sheet6]).Activate Worksheets(Array(Sheet4, Sheet5, Sheet6)).Activate Wrong set of Argument errors: Selection(Sheet4, Sheet5, Sheet6).Activate Sheets(Sheet4, Sheet5, Sheet6).Activate -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vba for selecting multiple sheets to save as a new book
THANKYOU Once Again!! works like a charm. Maybe someday I'll actually
understand it :) "Dave Peterson" wrote: If you're afraid of worksheet names changing, aren't you concerned that the sheets will be moved (left or right)??? Maybe using the codenames to get the sheet names would be safer. Option Explicit Sub testme02() Dim myCodeNames As Variant Dim iCtr As Long Dim mySheetNames() As String Dim mySht As Object Dim res As Variant With ActiveWorkbook myCodeNames = Array("Sheet1", "sheet3", "Sheet5") ReDim mySheetNames(1 To .Sheets.Count) iCtr = 0 For Each mySht In .Sheets res = Application.Match(mySht.CodeName, myCodeNames, 0) If IsError(res) Then 'no match, the board goes back Else iCtr = iCtr + 1 mySheetNames(iCtr) = mySht.Name End If Next mySht If iCtr = 0 Then MsgBox "no sheets to copy!" Else If UBound(myCodeNames) - LBound(myCodeNames) + 1 < iCtr Then MsgBox "Not all sheets found" Else ReDim Preserve mySheetNames(1 To iCtr) .Sheets(mySheetNames).Copy 'to a new workbook? End If End If End With End Sub Sharon wrote: After playing around some more, I found the following syntax works. Thanks for your time! Sheets(Array(4, 5, 6)).Select Sheets(Array(4, 5, 6)).Copy "Sharon" wrote: The tabs actually have text names, sheet4-6 refer to the sheet name when viewing the sheet in the vba editor. I wanted to use that in case we change a tab's name in the worksheet. I've tried your suggestion both in & without quotes,however, debug advises a "Type mismatch (Error 13) "Dave Peterson" wrote: How about treating those names as text and putting them in double quotes: Worksheets(Array("sheet4", "Sheet5", "Sheet6")).select (not .activate) If I were you, I'd drop the use of []'s. Sharon wrote: I searched through postings but couldn't find one which I could comprehend to address this issue. Earlier today, your support corrected my code in order to copy&save a single sheet to a new workbook. :) thankyou! Now I have several sheets from the book (over 50 sheets within the book) that we want to copy to a new book. Once again I tried to record the macro, then edit...but I receive a "compile error Sub or Function not defined" The following are the different ways we tried ; Compile errors: Selection([Sheet4], [Sheet5], [Sheet6]).Activate Worksheets(Array(Sheet4, Sheet5, Sheet6)).Activate Wrong set of Argument errors: Selection(Sheet4, Sheet5, Sheet6).Activate Sheets(Sheet4, Sheet5, Sheet6).Activate -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loading Data into Multiple sheets in 1 book | New Users to Excel | |||
Loading Data into Multiple sheets in 1 book | Setting up and Configuration of Excel | |||
Loading Data into Multiple sheets in 1 book | Excel Discussion (Misc queries) | |||
Selecting Multiple Sheets in VBA | Excel Worksheet Functions | |||
selecting multiple sheets | Excel Worksheet Functions |