VBA to copy multiple worksheets to new workbook

[bradsalmon]

Hi all,

Saw the below and thought it would solve my requirement but does anyone know for sure if it works in VBA as it was found under a VB help file on Microsoft.com -
Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Copy

If I type the code in the immediate window I can get it to work with both a string and ordinals representing the worksheets however my code keeps erroring with the substring out of range error with everything I try. I've tried both single quote and double quote so far but now at the end of my ideas before I rewrite the code and solve the problem an alternative way instead.

Can anyone see something obviously wrong in the below snippet -
'ensure worksheet exists
If chk_wrksht(mywrksht.Cells(i, 1)) Then
If mystr = "" Then
'single sheet to be copied
mystr = mywrksht.Cells(i, 1)
Else
'multiple sheets so build string for array
mystr = mystr & Chr(39) & ", " & Chr(39) & mywrksht.Cells(i, 1)
End If
End If
If InStr(1, mystr, ",") > 0 Then
'multiple worksheets so copy array approach
mystr = Chr(39) & mystr & Chr(39)
MNGTwrkbk.Worksheets(Array(mystr)).Copy
Else
'single sheet
MNGTwrkbk.Worksheets(mystr).Copy
End If

The single sheet works perfectly everytime and it is just the multiples that throw the error. I've even paused the code and queried mystr which looks fine, plus then added activeworkbook.worksheets(Array( )).Copy around it and it works fine. Just can't seem to figure out why it won't pass in run time!!!

Thank you,
Brad

 

[bradsalmon]

Hi all,

Saw the below and thought it would solve my requirement but does anyone know for sure if it works in VBA as it was found under a VB help file on Microsoft.com -
Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Copy

If I type the code in the immediate window I can get it to work with both a string and ordinals representing the worksheets however my code keeps erroring with the substring out of range error with everything I try. I've tried both single quote and double quote so far but now at the end of my ideas before I rewrite the code and solve the problem an alternative way instead.

Can anyone see something obviously wrong in the below snippet -
'ensure worksheet exists
If chk_wrksht(mywrksht.Cells(i, 1)) Then
If mystr = "" Then
'single sheet to be copied
mystr = mywrksht.Cells(i, 1)
Else
'multiple sheets so build string for array
mystr = mystr & Chr(39) & ", " & Chr(39) & mywrksht.Cells(i, 1)
End If
End If
If InStr(1, mystr, ",") > 0 Then
'multiple worksheets so copy array approach
mystr = Chr(39) & mystr & Chr(39)
MNGTwrkbk.Worksheets(Array(mystr)).Copy
Else
'single sheet
MNGTwrkbk.Worksheets(mystr).Copy
End If

The single sheet works perfectly everytime and it is just the multiples that throw the error. I've even paused the code and queried mystr which looks fine, plus then added activeworkbook.worksheets(Array( )).Copy around it and it works fine. Just can't seem to figure out why it won't pass in run time!!!

Thank you,
Brad

UPDATE: after some cursing and a cup of tea to calm down it looks as if I may have found a solution. Rather than trying to form an array by completing a string, it appears to work by using the Split function to make the array for you. I'm pretty sure the code can be simplified even further but my current working code is as below -
'ensure worksheet exists
If chk_wrksht(mywrksht.Cells(i, 1)) Then
If mystr = "" Then
'single sheet to be copied
mystr = mywrksht.Cells(i, 1)
Else
'multiple sheets so build string for array
mystr = mystr & "|" & mywrksht.Cells(i, 1)
End If
End If
If InStr(1, mystr, "|") > 0 Then
'multiple worksheets so copy array approach
MNGTwrkbk.Worksheets(Split(mystr, "|")).Copy
Else
'single sheet
MNGTwrkbk.Worksheets(mystr).Copy
End If

Thank you to anyone that took the time to look and hopefully the above will help someone in the future.
Brad