VBA Sheets(Array).Select mismatch

[rrichardlee33]

Hi - I am trying to copy and save a number of sheet groups (i.e. sheet 1, 2, 3, and 5 save; sheet 6, 7, 8, and 10 save) and a loop to do this. However I keep getting mismatch on the Sheets(array) section. Any help would be appreciated.

Sub Tabs()
'
' To automatically copy tabs into new excel and save
'
Dim FileName1 As String
Dim FilePath As String
Dim FileName2 As String
Dim TabsArray As Variant

FileName1 = Range("B7") 'file name to copy from
FilePath = Range("B8") 'where file will be saved

Windows("Tab Seperate.xlsm").Activate 'where the code is

For i = 11 To 18 'rows 11 through 18, the save as file names

FileName2 = Cells(i, 2)

TabsArray = Array(Cells(i, 3), Cells(i, 4), Cells(i, 5), Cells(i, 6), Cells(i, 7))

Windows(FileName1).Activate

Sheets(TabsArray).Copy

ActiveWorkbook.SaveAs FileName:= _
FilePath & FileName2

Next

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
You need to do it like

TabsArray = Array(Cells(i, 3).Value, Cells(i, 4).Value, Cells(i, 5).Value, Cells(i, 6).Value, Cells(i, 7).Value)

Otherwise its putting the range object into the array, rather than the values

 

[rrichardlee33]

Got it - Thank you so much. It worked like a charm.

 

[Fluff]

You're welcome & thanks for the feedback