• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining Rows of Data

grumpus

New Member
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.
 

Attachments

  • ChandooFile.xlsx
    35.5 KB · Views: 0
the following VBA will do what you want:

Code:
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:
 

Attachments

  • ChandooFile.xlsm
    45.8 KB · Views: 0
Last edited:
Back
Top