Combination of multiple excel workbooks with multiple sheets (not in a single sheet)

LaSta95 

Just look at my reply two or three up in this topic.

but there is no wbkS.Path in the macro.

LaSta95 

Change wbkS.Path to the folder path you want to use, for example C:\Excel

Hello Hans,

works like a charm, thank you!
Another quick question: Right now it saves the MasterA and B on my personal SharePoint (didn't change a thing). How can I change the save directory to a local drive?

LaSta95 

Change the line

    wbkT.Close SaveChanges:=True, Filename:=strA

to

    wbkT.SaveAs wbkS.Path & "\" & strA
    wbkT.Close

and

    wbkT.Close SaveChanges:=True, Filename:=strB

to

    wbkT.SaveAs wbkS.Path & "\" & strA
    wbkT.Close

Does that work?

"the document was not saved"

LaSta95 

Please tell us what the error message says.

Hello Hans, now I get an error here:
wbkT.Close SaveChanges:=True, Filename:=strA

LaSta95 

Try this version:

Sub SplitAB()
    Const lngSplit = 78 ' Column BZ
    Const strA = "MasterA.xlsx" ' Name of first master workbook
    Const strB = "MasterB.xlsx" ' Name of second master workbook
    Dim wbkS As Workbook
    Dim wbkT As Workbook
    Dim wshT As Worksheet
    Dim lngT As Long
    Application.ScreenUpdating = False
    Set wbkS = ActiveWorkbook
    ' Handle A
    wbkS.Worksheets.Copy
    Set wbkT = ActiveWorkbook
    For Each wshT In wbkT.Worksheets
        If wshT.UsedRange.Rows.Count > 1 Then
            wshT.UsedRange.AutoFilter Field:=lngSplit, Criteria1:="B"
            wshT.UsedRange.Offset(1).EntireRow.Delete
            wshT.UsedRange.AutoFilter
        End If
    Next wshT
    wbkT.Close SaveChanges:=True, Filename:=strA
    ' Handle B
    wbkS.Worksheets.Copy
    Set wbkT = ActiveWorkbook
    For Each wshT In wbkT.Worksheets
        If wshT.UsedRange.Rows.Count > 1 Then
            wshT.UsedRange.AutoFilter Field:=lngSplit, Criteria1:="A"
            wshT.UsedRange.Offset(1).EntireRow.Delete
            wshT.UsedRange.AutoFilter
        End If
    Next wshT
    wbkT.Close SaveChanges:=True, Filename:=strB
    Application.ScreenUpdating = True
End Sub

Hello Hans,

yes, there could be (temporary) empty sheets in the source workbook.

LaSta95 

Could there be empty sheets in the source workbook?

 

It would be helpful if you attached a sample workbook without sensitive data that demonstrates the problem.

Hello Hans,

wshT.UsedRange.AutoFilter Field:=lngSplit, Criteria1:="B"