vba for selecting multiple sheets to save as a new book

[Guest]

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]

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.

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

 

[Guest]

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)

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.

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

 

[Guest]

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

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)

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.

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]

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

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

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)

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

 

[Guest]

THANKYOU Once Again!! works like a charm. Maybe someday I'll actually
understand it

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

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

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)



:

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