Combining Rows of Data

[grumpus]

I get a file every month with several tabs and varying amounts of addresses. I'm looking to simplify or automate a copy/paste process. Each sheet could contain up to 90000 records. My sample file uses random numbers for each region. I would like to take the data from each worksheet and make one long list in the "Combined" worksheet.

I've found it easy enough to do by recording a macro but it doesn't work if I was to adjust the data from the Chicago tab to simulate the next month data if I had 200 records instead of 367. I may have 1000 records in that tab the following month. I need it to work no matter the number of records be worksheet every month.

Thanks in advance for your assistance.

 

[Hui]

the following VBA will do what you want:

Sub CombineData()

Dim sh As Worksheet
For Each sh In Worksheets
  If sh.Name <> "Combined" Then
  LR = sh.Range("A" & Rows.Count).End(xlUp).Row
  LRM = Worksheets("Combined").Range("A" & Rows.Count).End(xlUp).Row
  
  sh.Select
  Range(Cells(1, 1), Cells(LR, 1)).Copy Destination:=Worksheets("Combined").Range("A" & LRM + 1)
  
  Application.CutCopyMode = False
  End If
Next

End Sub

Copy and paste that into a Code Mode in VBA
Run it with F5

or see attached file:

 

[grumpus]

I would have never figured that out. Many thanks.