Combining data

terryibrennan

New Member
Joined
Apr 14, 2003
Messages
2
I have data in several worksheets, numbered 1-8. I want to combine all of it into another worksheet called ALLDATA. Each worksheet is a list of addresses in particular categories, and I simply want to combine all the categories into a master list. Ideally, I'd like the master list, ALLDATA, to auto update as data is added into the slave sheets. That is, I want worksheet ALLDATA to automatically receive all data entered into worksheets 1-8. I don't want to perform an maniuplations on the data, just want it all lumped together
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
try this:

Code:
Sub Macro1()
    'Change the name Sheet1 to the name of your target sheet
    Dim Sht As Worksheet
    Sheets("Sheet1").Cells.Clear
    For Each Sht In ThisWorkbook.Worksheets
        If Sht.Name <> "Sheet1" Then
            Range(Sht.[a1], Sht.[a65536].End(xlUp).End(xlToRight)).Copy
            Sheets("Sheet1").[a65536].End(xlUp).Paste
        End If
    Next Sht
        
End Sub

Note: I have assumed that data in the sheets is evenly laid out i.e., last row of data in colA corresponds to last row of data in other columns.
Remember to change "Sheet1" to the name of your target sheet.
Every time the code is run, all the data is freshly copied after deleting the existing. You may like to take a backup before running the code.
 

Learning Excel

Board Regular
Joined
Jul 27, 2002
Messages
99
Hi,


How would one look for the last row if the last row data is not always in Col A ?
It may happen to any columns say between Col A to Col AZ


Learning Excel
 

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
A loop thorugh the columns will be required:

Code:
Sub LastRow()
    Dim LRow As Long, LastLrow As Long
    Dim Col As Range
    For Each Col In [A:C].Columns
        LRow = Col.Cells(65536, 1).End(xlUp).Row
        If LRow > LastLrow Then LastLrow = LRow
    Next Col
    MsgBox "Last Row of data is: " & LastLrow
End Sub

Be aware that cells with any entries like invisible spaces, formulas (even if they return blanks) will be treated as having entries.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,982
Members
414,489
Latest member
Xlambda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top