unknownymous
Board Regular
- Joined
- Sep 19, 2017
- Messages
- 249
- Office Version
- 2016
- Platform
- Windows
Hi Guys,
Happy New year!
I found below code however, it seems like only column A of each sheet is being copied on the combined Sheet. Is it possible to adjust the range and copy everything except Row 1 for each sheet?
= = = = =
Sub Combine_Sheets()
Dim startRow, startCol, LastRow, lastCol As Long
Dim headers As Range
'Set Master sheet for consolidation
Set mtr = Worksheets("Combined")
Set wb = ThisWorkbook
'Get Headers
Set headers = Application.InputBox("Select the Headers", Type:=8)
'Copy Headers into master
headers.Copy mtr.Range("A1")
startRow = headers.Row + 1
startCol = headers.Column
Debug.Print startRow, startCol
'loop through all sheets
For Each ws In wb.Worksheets
'except the master sheet from looping
If ws.Name <> "Combined" Then
ws.Activate
LastRow = Cells(Rows.Count, startCol).End(xlUp).Row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
'get data from each worksheet and copy it into Master sheet
Range(Cells(startRow, startCol), Cells(LastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Next ws
Worksheets("Combined").Activate
End Sub
Any help will be much appreciated.
Regards!
= = =
Source: Consolidate/Merge multiple worksheets into one master sheet using VBA
= = =
Happy New year!
I found below code however, it seems like only column A of each sheet is being copied on the combined Sheet. Is it possible to adjust the range and copy everything except Row 1 for each sheet?
= = = = =
Sub Combine_Sheets()
Dim startRow, startCol, LastRow, lastCol As Long
Dim headers As Range
'Set Master sheet for consolidation
Set mtr = Worksheets("Combined")
Set wb = ThisWorkbook
'Get Headers
Set headers = Application.InputBox("Select the Headers", Type:=8)
'Copy Headers into master
headers.Copy mtr.Range("A1")
startRow = headers.Row + 1
startCol = headers.Column
Debug.Print startRow, startCol
'loop through all sheets
For Each ws In wb.Worksheets
'except the master sheet from looping
If ws.Name <> "Combined" Then
ws.Activate
LastRow = Cells(Rows.Count, startCol).End(xlUp).Row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
'get data from each worksheet and copy it into Master sheet
Range(Cells(startRow, startCol), Cells(LastRow, lastCol)).Copy _
mtr.Range("A" & mtr.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Next ws
Worksheets("Combined").Activate
End Sub
Any help will be much appreciated.
Regards!
= = =
Source: Consolidate/Merge multiple worksheets into one master sheet using VBA
= = =