Forum Discussion
LaSta95
Apr 02, 2021Copper Contributor
Combination of multiple excel workbooks with multiple sheets (not in a single sheet)
Hello! 🙂 I am struggeling right now with a task. I try to combine multiple excel workbooks, all with the same structure, so f.e. 5 sheets, each with the same headers, simultan over each workbook,...
LaSta95
Apr 12, 2021Copper Contributor
Hello Hans,
yes, there could be (temporary) empty sheets in the source workbook.
yes, there could be (temporary) empty sheets in the source workbook.
HansVogelaar
Apr 12, 2021MVP
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- HansVogelaarApr 14, 2021MVP
Just look at my reply two or three up in this topic.
- LaSta95Apr 14, 2021Copper Contributorbut there is no wbkS.Path in the macro.
- HansVogelaarApr 13, 2021MVP
Change wbkS.Path to the folder path you want to use, for example C:\Excel
- LaSta95Apr 13, 2021Copper ContributorHello 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? - HansVogelaarApr 12, 2021MVP
Change the line
wbkT.Close SaveChanges:=True, Filename:=strAto
wbkT.SaveAs wbkS.Path & "\" & strA wbkT.Closeand
wbkT.Close SaveChanges:=True, Filename:=strBto
wbkT.SaveAs wbkS.Path & "\" & strA wbkT.CloseDoes that work?
- LaSta95Apr 12, 2021Copper Contributor"the document was not saved"
- HansVogelaarApr 12, 2021MVP
Please tell us what the error message says.
- LaSta95Apr 12, 2021Copper ContributorHello Hans, now I get an error here:
wbkT.Close SaveChanges:=True, Filename:=strA