Combining data

[terryibrennan]

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

 

[P Sitaram]

try this:

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]

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]

A loop thorugh the columns will be required:

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.