I have been working on some combining scripts. If the source worksheets contain the SAME columns as each other and as the Master worksheet, this code works:
Code
Sub CombineData()
Dim Sht As Worksheet
'This If will clear Master before combining
Worksheets("Master").Range("A2:M65536").ClearContents
'This For loop will combine the worksheets into 'Master'
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" And Sht.Range("A2").Value <> "" Then
Sht.Select
LastRow = Range("A65536").End(xlUp).Row
Range("A2", Cells(LastRow, "M")).Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
'The below command will paste values-only withOUT calculations:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'This command would clear the source worksheet after copy
'Range("A2", Cells(LastRow, "M")).ClearContents
Else
End If
Next Sht
'These commands will delete columns and shift left
'Ranges work such as "A:C" or single "A"
'Suggest doing single columns working right to left to account for restructure
'Worksheets("Master").Columns("K").EntireColumn.Delete
'Worksheets("Master").Columns("G").EntireColumn.Delete
End SubHowever, I have taken this a step further and have source worksheets with some different and some of the same columns. And I now need to combine those into the Master worksheet.
I have attached the sample worksheet I'm working with. I'm open to either mapping all source to destination columns or more preferably, creating master by looking up columns, if present, put data there and if column not present, adding it to the end... rinse, was, repeat.
Thanks in advance!