VBA - Add sheets to variable and move to new workbook

Question

I am trying to write a macro which can create up to 3 new worksheets, and at the end will move those sheets to a new workbook. Some, all or none of those sheets may be created, and I am struggling to move them all. I have tried adding their names to both a string and an array (with and without quotes to mimic if I manually typed the names of the sheets to move).

This is what I have at the moment, but it is returning Run-Time error '9': Subscript out of range. If I only move one of the sheets this works fine, but as soon as there are multiple sheets it stops working. Please could someone advise?

Dim SheetNames As String  

if x is True Then
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "a"
    SheetNames = Worksheet.Name
End if
if y is True Then
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "b"
    If SheetNames = "" Then
        SheetNames = Worksheet.Name
    Else
        SheetNames = sheetNames & Worksheet.Name
    End if
End if
if z is True Then
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "c"
    If SheetNames = "" Then
        SheetNames = Worksheet.Name
    Else
        SheetNames = sheetNames & Worksheet.Name
    End if
End if

Sheets(Array(SheetNames)).Move

you need commas between your sheet names, like: `SheetNames = sheetNames & "," & Worksheet.Name` — Scott Holtzman, Aug 28 '18 at 12:49
why not create the worksheets in a new workbook instead of the "runaround"? — ashleedawg, Aug 28 '18 at 12:57
**There are several issues in your code** that would be made clear to you by adding the line `Option Explicit` to the top of [every] module [always]. Among other problems are undeclared and misused variables, `Is True` is incorrect syntax (should be `If x = True Then` or simply `If x Then`, unknown expression `Worksheet.Name` (used several times), incorrect usage of `Move`. I'f suggest you Google every term that you do not thoroughly understand, and would also benefit from a VBA tutorial. — ashleedawg, Aug 28 '18 at 13:01
Also, you'll need to use the Split function in order to return an array of sheet names, and then you can use the Copy method of the Sheets object --> `Sheets(Split(SheetNames, ",")).Copy`. — Domenic, Aug 28 '18 at 13:11

score 1 · Answer 1 · answered Aug 28 '18 at 13:21

You can't pass a string holding all the sheet names inside the Array() call.

What you can do is to create an array to hold the name of the worksheets you want to move, and call the move method afterwards:

Sub example()

Dim x As Boolean, y As Boolean, z As Boolean

Dim SheetNamesArr() As String
Dim ArrSize As Integer

x = True
y = True
z = True

ArrSize = 0
ReDim Preserve SheetNamesArr(ArrSize)

If x Then
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "a"
    ReDim Preserve SheetNamesArr(0 To ArrSize)
    SheetNamesArr(ArrSize) = ActiveSheet.Name
    ArrSize = ArrSize + 1
End If

If y Then
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "b"
    ReDim Preserve SheetNamesArr(0 To ArrSize)
    SheetNamesArr(ArrSize) = ActiveSheet.Name
    ArrSize = ArrSize + 1
End If

If z Then
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "c"
    ReDim Preserve SheetNamesArr(0 To ArrSize)
    SheetNamesArr(ArrSize) = ActiveSheet.Name
    ArrSize = ArrSize + 1
End If

Sheets(SheetNamesArr).Move

End Sub

Thanks so much! I thought that it should have been done using an array but they aren't my strong suit. This has worked perfectly — Suzy, Aug 28 '18 at 14:34

VBA - Add sheets to variable and move to new workbook

1 Answers1

Linked