Posts by mr_gustaveh

    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]