Im having two issues
Issue 1) at the top of my "IMPORT" sheet its duplicating the header name in A1 so that it is appearing twice once in cell A1 where it should be and in A2.
Issue 2) I want to change this to actually pastes values into the "Import"
Here is my code
Sub Merge_Sheets()
Dim startRow, startCol, lastRow, lastCol As Long
Dim headers As Range
'Set Master sheet for consolidation
Set mtr = Worksheets("IMPORT")
Set wb = ThisWorkbook
'Get Headers
Set headers = Worksheets("ImportM1").Range("A1:K1")
'Copy Headers into master
headers.Copy mtr.Range("A1")
startRow = headers.Row + 1
startCol = headers.Column
'loop through all sheets
For Each ws In wb.Worksheets
'except the master sheet from looping
If ws.Name <> "Import" And ws.Name <> "Paste Here" And ws.Name <> "M3" And ws.Name <> "M2" And ws.Name <> "M1" And ws.Name <> "Vlookups" And ws.Name <> "Validation" And ws.Name <> "Fail" 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("IMPORT").Activate
End Sub
Bookmarks