VBA Sheets(Array).Select mismatch

rrichardlee33

New Member
Joined
Oct 19, 2018
Messages
2
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,650
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You need to do it like
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,650
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,523
Messages
5,529,334
Members
409,863
Latest member
stacy09
Top