Aggregating Data with Same Column Header from multiple sheets into one

[chails93]

Hi!

So I have a data dump from two separate applications that are identically formatted... there are 20 columns in each sheet with the same headers, and I need to create a command button that will allow me to aggregate those two sheets into one sheet. The Data from sheet number one titled "Data EAST" should be copied first, followed by the data from sheet number two titled "Data WEST".

Im sure this is pretty rudimentary for most VBA users, but im a newbie and need some help!

Thanks
conor

 

[vcoolio]

Hello Conor,

See if the following code works for you:-

Sub Consolidate()

Dim ws As Worksheet, sh As Worksheet
Set sh = Sheets("Main")

Application.ScreenUpdating = False

sh.UsedRange.Offset(1).ClearContents

For Each ws In Worksheets
If ws.Name <> "Main" Then
      ws.UsedRange.Offset(1).Copy
           sh.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
      End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code assumes:-

- Row 1 in each sheet has headings, starting in Column A.
- Data starts in row 2, Column A.

Change Sheets("Main") to whatever the name of your destination sheet is.

I hope that this helps.

Cheerio,
vcoolio.