Hi all,
As the title mentions, I am trying to break out a file into individual workbooks. I have been successful breaking out the first workbook, but I keep getting a run-time error when it needs to break out the second workbook. I think it might be an issue with the fifth tab, but would appreciate any help.
[VBA]Sub Split_MasterPFSP12062017()
Dim SaveSheet As Integer
Application.ScreenUpdating = False
'IndexRow is the first data row in the 'Index Table'
IndexRow = 2
' Note Workbook name is this Excel Sheet's name
Horizontal = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 1)
'TabName 1-4 are the names of the tabs created in the split file
TabName1 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 2)
TabName2 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 3)
'------------------------------------
TabName3 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 4)
TabName4 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 5)
'--------------------------------------
TabName5 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 6)
' FolderName is the folder you want to save the split file in
FolderName = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 9)
' Give name to the Split file
Filename = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 10)
' Number of Columns in the data source
TotalColumns_tab1 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 11)
TotalColumns_tab1 = TotalColumns_tab1 + 1
' Where in the data source file the Horizontal column is located
HorizontalColumn_tab1 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 12)
'------------------------------------------------------------------
' Same things for the 2nd data source file
TotalColumns_tab2 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 14)
TotalColumns_tab2 = TotalColumns_tab2 + 1
HorizontalColumn_tab2 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 15)
'------------------------------------------------------------------------
' Name of the Data source Sheet
SourceReport1 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 13)
SourceReport2 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 16)
'----3nd data source tab---------------------------------
TotalColumns_tab3 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 17)
TotalColumns_tab3 = TotalColumns_tab3 + 1
HorizontalColumn_tab3 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 18)
SourceReport3 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 19)
' 4th data source tab
TotalColumns_tab4 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 20)
TotalColumns_tab4 = TotalColumns_tab4 + 1
HorizontalColumn_tab4 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 21)
SourceReport4 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 22)
' 5th data source tab
TotalColumns_tab5 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 23)
TotalColumns_tab5 = TotalColumns_tab5 + 1
HorizontalColumn_tab5 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 24)
SourceReport5 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(2, 25)
'--------------------------------------
SaveSheet = 0
' Loop through all the non-empty rows of Index Table
While Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 1) <> ""
If Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 1) = Horizontal Then
Set ws = Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport1)
Workbooks("P&F Split Macro Test1.xlsm").Activate
' Create copy of the data source report
ws.Copy After:=Workbooks("P&F Split Macro Test1.xlsm").Sheets(Sheets.Count)
ActiveSheet.Name = TabName1
'Delete all contents of the copied sheet but keep the column headers
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName1).Range("A2:AZ100000").ClearContents
'Adjust the row.No where data starts
SourceReportRow1 = 2
TargetReportRow1 = 2
'---------------------------same for second tab
Set ws2 = Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport2)
Workbooks("P&F Split Macro Test1.xlsm").Activate
ws2.Copy After:=Workbooks("P&F Split Macro Test1.xlsm").Sheets(Sheets.Count)
ActiveSheet.Name = TabName2
'Adjust the row.No where data starts
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName2).Range("A2:AZ100000").ClearContents
SourceReportRow2 = 2
TargetReportRow2 = 2
'---------------------------------------------------------
'------------------------------------for tab3
Set ws3 = Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport3)
Workbooks("P&F Split Macro Test1.xlsm").Activate
ws3.Copy After:=Workbooks("P&F Split Macro Test1.xlsm").Sheets(Sheets.Count)
ActiveSheet.Name = TabName3
'Adjust for how many columns/rows are in the report
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName3).Range("A2:AZ100000").ClearContents
SourceReportRow3 = 2
TargetReportRow3 = 2
'-------------------------------------------------
'------------------------------------for tab4
Set ws3 = Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport4)
Workbooks("P&F Split Macro Test1.xlsm").Activate
ws3.Copy After:=Workbooks("P&F Split Macro Test1.xlsm").Sheets(Sheets.Count)
ActiveSheet.Name = TabName4
'Adjust for how many columns/rows are in the report
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName4).Range("A2:AZ100000").ClearContents
SourceReportRow4 = 2
TargetReportRow4 = 2
'-------------------------------------------------
'------------------------------------for tab5
Set ws3 = Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport5)
Workbooks("P&F Split Macro Test1.xlsm").Activate
ws3.Copy After:=Workbooks("P&F Split Macro Test1.xlsm").Sheets(Sheets.Count)
ActiveSheet.Name = TabName5
'Adjust for how many columns/rows are in the report
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName5).Range("A2:AZ100000").ClearContents
SourceReportRow5 = 2
TargetReportRow5 = 2
'-------------------------------------------------
' The following While loop copies horizontal info to the newly copied data source sheet (note here all contents in that tab have been cleared
While Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport1).Cells(SourceReportRow1, 1) <> ""
'Adjust for which column contains the Horizontal name
If Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport1).Cells(SourceReportRow1, HorizontalColumn_tab1) = Horizontal Then
TargetReportColumn1 = 1
SourceReportColumn1 = 1
'Adjust for total number of columns within the report you want to copy over
Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport1).Cells(SourceReportRow1, TotalColumns_tab1) = "x"
While TargetReportColumn1 < TotalColumns_tab1
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName1).Cells(TargetReportRow1, TargetReportColumn1) = Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport1).Cells(SourceReportRow1, SourceReportColumn1)
TargetReportColumn1 = TargetReportColumn1 + 1
SourceReportColumn1 = SourceReportColumn1 + 1
Wend
TargetReportRow1 = TargetReportRow1 + 1
End If
SourceReportRow1 = SourceReportRow1 + 1
Wend
'---------------------------for second tab
While Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport2).Cells(SourceReportRow2, 1) <> ""
'Adjust for which column contains the Horizontal name
If Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport2).Cells(SourceReportRow2, HorizontalColumn_tab2) = Horizontal Then
TargetReportColumn2 = 1
SourceReportColumn2 = 1
'Adjust for total number of columns within the report you want to copy over
Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport2).Cells(SourceReportRow2, TotalColumns_tab2) = "x"
While TargetReportColumn2 < TotalColumns_tab2
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName2).Cells(TargetReportRow2, TargetReportColumn2) = Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport2).Cells(SourceReportRow2, SourceReportColumn2)
TargetReportColumn2 = TargetReportColumn2 + 1
SourceReportColumn2 = SourceReportColumn2 + 1
Wend
TargetReportRow2 = TargetReportRow2 + 1
End If
SourceReportRow2 = SourceReportRow2 + 1
Wend
'------------------------------------------------------------------
'-----------------for tab3--------------------------------------
While Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport3).Cells(SourceReportRow3, 1) <> ""
'Adjust for which column contains the Horizontal name
If Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport3).Cells(SourceReportRow3, HorizontalColumn_tab3) = Horizontal Then
TargetReportColumn3 = 1
SourceReportColumn3 = 1
'Adjust for total number of columns within the report you want to copy over
Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport3).Cells(SourceReportRow3, TotalColumns_tab3) = "x"
While TargetReportColumn3 < TotalColumns_tab3
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName3).Cells(TargetReportRow3, TargetReportColumn3) = Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport3).Cells(SourceReportRow3, SourceReportColumn3)
TargetReportColumn3 = TargetReportColumn3 + 1
SourceReportColumn3 = SourceReportColumn3 + 1
Wend
TargetReportRow3 = TargetReportRow3 + 1
End If
SourceReportRow3 = SourceReportRow3 + 1
Wend
'--------------------------------------------------------------------------
'-----------------for tab4--------------------------------------
While Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport4).Cells(SourceReportRow4, 1) <> ""
'Adjust for which column contains the Horizontal name
If Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport4).Cells(SourceReportRow4, HorizontalColumn_tab4) = Horizontal Then
TargetReportColumn4 = 1
SourceReportColumn4 = 1
'Adjust for total number of columns within the report you want to copy over
Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport4).Cells(SourceReportRow4, TotalColumns_tab4) = "x"
While TargetReportColumn4 < TotalColumns_tab4
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName4).Cells(TargetReportRow4, TargetReportColumn4) = Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport4).Cells(SourceReportRow4, SourceReportColumn4)
TargetReportColumn4 = TargetReportColumn4 + 1
SourceReportColumn4 = SourceReportColumn4 + 1
Wend
TargetReportRow4 = TargetReportRow4 + 1
End If
SourceReportRow4 = SourceReportRow4 + 1
Wend
'--------------------------------------------------------------------------
'-----------------for tab5--------------------------------------
While Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport5).Cells(SourceReportRow5, 1) <> ""
'Adjust for which column contains the Horizontal name
If Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport5).Cells(SourceReportRow5, HorizontalColumn_tab5) = Horizontal Then
TargetReportColumn5 = 1
SourceReportColumn5 = 1
'Adjust for total number of columns within the report you want to copy over
Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport5).Cells(SourceReportRow5, TotalColumns_tab5) = "x"
While TargetReportColumn5 < TotalColumns_tab5
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName5).Cells(TargetReportRow5, TargetReportColumn5) = Workbooks("P&F Split Macro Test1.xlsm").Sheets(SourceReport5).Cells(SourceReportRow5, SourceReportColumn5)
TargetReportColumn5 = TargetReportColumn5 + 1
SourceReportColumn5 = SourceReportColumn5 + 1
Wend
TargetReportRow5 = TargetReportRow5 + 1
End If
SourceReportRow5 = SourceReportRow5 + 1
Wend
'Adjust for how many columns/rows are in the report
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName1).Rows(TargetReportRow1 & ":100000").Delete
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName1).Columns(TargetReportColumn1).Delete
'--------------------------------------------
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName2).Rows(TargetReportRow2 & ":100000").Delete
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName2).Columns(TargetReportColumn2).Delete
'-------------------------------------------------
'-----------------tab 3---------------------------
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName3).Rows(TargetReportRow3 & ":100000").Delete
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName3).Columns(TargetReportColumn3).Delete
'-------------------------------------------------
'-----------------tab 4---------------------------
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName4).Rows(TargetReportRow4 & ":100000").Delete
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName4).Columns(TargetReportColumn4).Delete
'-------------------------------------------------
'-----------------tab 5---------------------------
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName5).Rows(TargetReportRow5 & ":100000").Delete
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName5).Columns(TargetReportColumn5).Delete
'-------------------------------------------------
'Add tab to newly created workbook
Windows("P&F Split Macro Test1.xlsm").Activate
Sheets(TabName1).Select
If TargetReportRow1 <> 0 Then
If SaveSheet = 0 Then
Set w = Application.Workbooks.Add
'Adjust for whoever's computer is running Macro
w.SaveAs Filename:="C:\Users\u516465\Desktop\Excel Split&Merge\" & FolderName & "\" & Filename & ".xlsb", FileFormat:=50
SaveSheet = 1
End If
'Adjust for which workbooks you want copied over/if you want them to be hidden, etc
Workbooks("P&F Split Macro Test1.xlsm").Activate
Sheets(TabName1).Copy Before:=Workbooks(Filename & ".xlsb").Sheets(1)
'------------------------------------------------------
Workbooks("P&F Split Macro Test1.xlsm").Activate
Sheets(TabName2).Copy Before:=Workbooks(Filename & ".xlsb").Sheets(2)
'-------------------------------------------------------
'-------------------tab3 -----------------------------------
Workbooks("P&F Split Macro Test1.xlsm").Activate
Sheets(TabName3).Copy After:=Workbooks(Filename & ".xlsb").Sheets(3)
'-------------------------------------------------------
'-------------------tab4 -----------------------------------
Workbooks("P&F Split Macro Test1.xlsm").Activate
Sheets(TabName4).Copy After:=Workbooks(Filename & ".xlsb").Sheets(4)
'-------------------------------------------------------
'-------------------tab5 -----------------------------------
Workbooks("P&F Split Macro Test1.xlsm").Activate
Sheets(TabName5).Copy After:=Workbooks(Filename & ".xlsb").Sheets(5)
'-------------------------------------------------------
Workbooks("P&F Split Macro Test1.xlsm").Activate
Sheets("0. Guidance").Copy Before:=Workbooks(Filename & ".xlsb").Sheets(1)
Sheets("0. Guidance").Visible = True
'--------------------------------------------------
'Workbooks("P&F Split Macro Test1.xlsm").Activate
'Sheets("RelationshipsPlansFunctionsRRPS").Copy After:=Workbooks(Filename & ".xlsb").Sheets(3)
'Sheets("RelationshipsPlansFunctionsRRPS").Visible = True
End If
'Delete tab in existing workbook
Application.DisplayAlerts = False
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName1).Delete
'--------------------------------
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName2).Delete
'---------------------------------
'-------- tab3 ------------------------
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName3).Delete
'---------------------------------
'-------- tab4 ------------------------
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName4).Delete
'---------------------------------
'-------- tab5 ------------------------
Workbooks("P&F Split Macro Test1.xlsm").Sheets(TabName5).Delete
'---------------------------------
Application.DisplayAlerts = True
' Now go to next Horizontal
IndexRow = IndexRow + 1
Else
'Delete sheet 1, 2 and 3 from newly created workbook
Application.DisplayAlerts = False
Workbooks(Filename & ".xlsb").Sheets("Sheet1").Delete
Application.DisplayAlerts = True
Workbooks(Filename & ".xlsb").Sheets(TabName1).Activate
Workbooks(Filename & ".xlsb").Sheets("0. Guidance").Activate
'Save newly created workbook in correct folder
Workbooks(Filename & ".xlsb").Save
'close newly created workbook
Workbooks(Filename & ".xlsb").Close
SaveSheet = 0
'Reassign values to parameters
Horizontal = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 1)
TabName1 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 2)
TabName2 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 3)
'------------------------tab3 -----------------
TabName3 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 4)
'-----------------------------------------------
'------------------------tab4 -----------------
TabName4 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 5)
'-----------------------------------------------
'------------------------tab5 -----------------
TabName4 = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 6)
'-----------------------------------------------
FolderName = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 9)
Filename = Workbooks("P&F Split Macro Test1.xlsm").Sheets("Index Table").Cells(IndexRow, 10)
SourceReportRow1 = 2
TargetReportRow1 = 2
SourceReportRow2 = 2
TargetReportRow2 = 2
SourceReportRow3 = 2
TargetReportRow3 = 2
SourceReportRow4 = 2
TargetReportRow4 = 2
SourceReportRow5 = 2
TargetReportRow5 = 2
End If
Wend
'Delete sheet 1, 2 and 3 for the last workbook
If SaveSheet = 1 Then
Application.DisplayAlerts = False
Workbooks(Filename & ".xlsb").Sheets("Sheet1").Delete
Workbooks(Filename & ".xlsb").Sheets("Sheet2").Delete
Workbooks(Filename & ".xlsb").Sheets("Sheet3").Delete
Workbooks(Filename & ".xlsb").Sheets("Sheet4").Delete
Workbooks(Filename & ".xlsb").Sheets("Sheet5").Delete
Application.DisplayAlerts = True
Workbooks(Filename & ".xlsb").Sheets(TabName1).Activate
Workbooks(Filename & ".xlsb").Sheets("0. Guidance").Activate
'Save newly created workbook in correct folder
Workbooks(Filename & ".xlsb").Save
'close newly created workbook
Workbooks(Filename & ".xlsb").Close
End If
Application.ScreenUpdating = True
End Sub[/VBA]