Tim Hanson
Member
Hello,
I am trying to save sheets to a new WB in the order they lised in the Array.
I get the first sheet in the If statment but it fails on the else statment I get "Script out of range" Here:
I can not figure out what I am doing wrong
Thanks
I am trying to save sheets to a new WB in the order they lised in the Array.
I get the first sheet in the If statment but it fails on the else statment I get "Script out of range" Here:
Code:
Worksheets(SheetsArr(I)).Copy _
Before:=Workbooks(GenWorkbookName).Sheets(SheetsArr(UBound(SheetsArr)))
Thanks
Code:
Sub SaveSheetsAsNewBookByList()
Dim SheetsArr As Variant
Dim ws As Worksheet, wsN As Worksheet
Dim WorkbookName As String, MyFilePath As String, FileName As String, GenWorkbookName As String
Dim I As Long
'goFast False
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
WorkbookName = "MyFile Name"
MyFilePath = ThisWorkbook.Path & "\" & WorkbookName
If Len(Dir(MyFilePath, vbDirectory)) = 0 Then
MkDir MyFilePath
End If
'Array of Sheet Names To Copy To New WB
SheetsArr = Array("DDD", "AAA", "ZZZ")
'Copy Over Sheets To New WB
For I = UBound(SheetsArr) To LBound(SheetsArr) Step -1
If I = UBound(SheetsArr) Then
Worksheets(SheetsArr(I)).Copy
'Get Name of the New Generated WB
GenWorkbookName = ActiveWorkbook.Name
Else
Worksheets(SheetsArr(I)).Copy Before:=Workbooks(GenWorkbookName).Sheets(SheetsArr(UBound(SheetsArr)))
End If
Next I
'Activate New Work Book
Workbooks(GenWorkbookName).Activate
With ActiveWorkbook
'~save book in this folder
ActiveWorkbook.SaveAs FileName:=MyFilePath & "\" & WorkbookName & "_" & Format(Now(), "DD-MM-YY hh.mm") & ".xlsx", FileFormat:=51
ActiveWorkbook.Close SaveChanges:=True
End With
Sheets(1).Select
'goFast True
End Sub