Merge 2 Worksheets with Similar Headings into 1 Master File

[iAmPatch]

Hi,

I've been trying to google and search the forum for a VBA code which would enable me to merge two worksheets with similar headings into one consolidated master file. But I can't seem to find the correct one. Can someone please help me?

My two worksheets are currently named as TrackerActive and TrackerArchive; all the information from both sheets should be consolidated / merged into a worksheet named TrackerMaster.

Thanks a lot to those who can help

 

[SpillerBD]

You can append to Excel Files to each other using Get&Transform.
Just which version of Excel do you have?

 

[JoeMo]

You didn't provide information on the layout you want for the master sheet. Here's some starter code for you that places the active sheet first starting in A1 followed by the archive sheet in col A first empty cell.

Sub CreateTrackerMaster()
Dim S1 As Worksheet, S2 As Worksheet, S3 As Worksheet, nxRw As Long
Set S1 = Sheets("TrackerActive")
Set S2 = Sheets("TrackerArchive")
On Error Resume Next
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
Sheets("TrackerMaster").Delete
Application.DisplayAlerts = True
Set S3 = Sheets.Add(before:=S1)
S3.Name = "TrackerMaster"
S1.UsedRange.Copy Destination:=S3.Range("A1")
nxRw = S3.UsedRange.Rows.Count + 1
S2.UsedRange.Copy Destination:=S3.Cells(nxRw, "A")
Application.ScreenUpdating = True
End Sub

 

[iAmPatch]

You can append to Excel Files to each other using Get&Transform.
Just which version of Excel do you have?

Get&Transform? I'm currently using Excel 2016

 

[iAmPatch]

You didn't provide information on the layout you want for the master sheet. Here's some starter code for you that places the active sheet first starting in A1 followed by the archive sheet in col A first empty cell.

Sub CreateTrackerMaster()
Dim S1 As Worksheet, S2 As Worksheet, S3 As Worksheet, nxRw As Long
Set S1 = Sheets("TrackerActive")
Set S2 = Sheets("TrackerArchive")
On Error Resume Next
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
Sheets("TrackerMaster").Delete
Application.DisplayAlerts = True
Set S3 = Sheets.Add(before:=S1)
S3.Name = "TrackerMaster"
S1.UsedRange.Copy Destination:=S3.Range("A1")
nxRw = S3.UsedRange.Rows.Count + 1
S2.UsedRange.Copy Destination:=S3.Cells(nxRw, "A")
Application.ScreenUpdating = True
End Sub

Thanks Joe. I'm sorry for not providing additional information. The TrackerMaster should be formatted as "Table" as there will be a need to add-in formulas once the merge / consolidation is finished.

 

[iAmPatch]

@JoeMo when I tried to run your code; it only copied information from the TrackerActive worksheet

It wasn't able to copy the information from TrackerArchive. Is it because TrackerArchive has row headings?

 

[SpillerBD]

Get&Transform? I'm currently using Excel 2016

Get &Transform was integrated in 2016. Previously known as Power Query.
It provides very much improved data import and manipulation from other sources, including Tables, other workbooks, text files and databases.
Mike Girvin, ExcelIsFun, has an on going series on the subject.
[h=1]<yt-formatted-string class="style-scope ytd-video-primary-info-renderer" style="--yt-endpoint-color:hsl(206.1, 79.3%, 52.7%);">Microsoft Power Tools for Data Analysis: Dashboards & Reports</yt-formatted-string>[/h]Bill, Mr Excel, has been impressed too. https://www.mrexcel.com/news/learn-power-query-this-year/

 

[JoeMo]

@JoeMo when I tried to run your code; it only copied information from the TrackerActive worksheet

It wasn't able to copy the information from TrackerArchive. Is it because TrackerArchive has row headings?

Can you explain what "wasn't able to copy the information from TrackerArchive" means? Did you get an error message? If so, what was it and what line was highlighted? If not, what exactly happens when you run the code? Is it possible the name of the archive sheet has an unwanted space at the start or end?

 

[iAmPatch]

Can you explain what "wasn't able to copy the information from TrackerArchive" means? Did you get an error message? If so, what was it and what line was highlighted? If not, what exactly happens when you run the code? Is it possible the name of the archive sheet has an unwanted space at the start or end?

TrackerActive has around 650 rows of data while TrackerArchive has about 4350 rows of data. The macro did not return any error at all. But the result file in TrackerMaster only has the 650 rows from TrackerActive and wasn't able to get the 4350 rows of data from TrackerArchive. There were no spaces in the names of the worksheets and even in the macro line.

 

[iAmPatch]

Get &Transform was integrated in 2016. Previously known as Power Query.
It provides very much improved data import and manipulation from other sources, including Tables, other workbooks, text files and databases.
Mike Girvin, ExcelIsFun, has an on going series on the subject.
[h=1]<yt-formatted-string class="style-scope ytd-video-primary-info-renderer" style="--yt-endpoint-color:hsl(206.1, 79.3%, 52.7%);">Microsoft Power Tools for Data Analysis: Dashboards & Reports</yt-formatted-string>[/h]Bill, Mr Excel, has been impressed too. https://www.mrexcel.com/news/learn-power-query-this-year/

I have to check on this thoroughly. I can't seem to find that option even when trying to check the advanced settings of Excel