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:
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 Sub
However, 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!