Рет қаралды 7,613
Excel Macro to combine data from Multiple Worksheets of a Workbook is explained in this video.
🍐Join my online course on Excel Tables
www.udemy.com/...
🍓Download my free eBook on Excel Macros
eepurl.com/gpT0RD
🍇Check these Books on Excel
xlncad.com/exc...
🍉Read my articles on Excel
xlncad.com/
🍏Playlist for Shorts on Excel
• Short videos on Excel ...
VBA code to combine data from Multiple Worksheets of an Excel Workbook
Sub Combine_WorkSheets()
Dim sRow, sCol, lRow, lCol As Long
Dim hdrs As Range
For Each Sheet In ActiveWorkbook.Worksheets
If Sheet.Name = "Master" Then
Application.DisplayAlerts = False
Worksheets("Master").Delete
Application.DisplayAlerts = True
End If
Next Sheet
Sheets.Add.Name = "Master"
Set mtr = Worksheets("Master")
Set wb = ActiveWorkbook
Set hdrs = Application.InputBox("Select the headers", Type:=8)
hdrs.Copy mtr.Range("A1")
sRow = hdrs.Row + 1
sCol = hdrs.Column
Debug.Print sRow, sCol
For Each ws In wb.Worksheets
If ws.Name (not equal to, replace with angled brackets) "Master" Then
ws.Activate
lRow = Cells(Rows.Count, sCol).End(xlUp).Row
lCol = Cells(sRow, Columns.Count).End(xlToLeft).Column
Range(Cells(sRow, sCol), Cells(lRow, lCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Next ws
Worksheets("Master").Activate
ActiveWindow.Zoom = 115
End Sub
#excelmacro #vbatutorial #combineworksheets