Pull data from separate tabs into one tab

Ok heres my problem im trying to automate my spreadsheet explanation follows.

Say if anything is populated in Sheet1 Column A-C then pull that through to Sheet3 A-C, after i want it to look in Sheet2 if there is anything populated in A-C in Sheet2 then pull that through to the next available row in Sheet3 Column A-C

Any ideas? please help! :D

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

I would normally do that with a macro. Is that what you had in mind?

The macro on the page Shockeroo noted above is pretty much what I had in mind, perhaps a little tweaking based on your workbook, or perhaps an automatic macro that keeps sheet3 up to date all by itself.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

Sorry i did this the first time but had to relog in before i could post it, it slipped my mind after re-writing my post, but anyway.

I followed the link advised by Shockeroo and modified it for my worksheet, which it works perfectly thank you for that except the only thing i can't figure out is how to change the code to include all rows and not just the one row as stated in the code below.

Option Explicit

Sub Consolidate()
Dim OutSH   As Worksheet
Dim ws      As Worksheet

Set OutSH = Sheets("All")
OutSH.Cells.ClearContents
OutSH.Range("A1:H1").Value = Array("Employee ID", "Employee Name", "", "Fund", "", "Accounting Unit", "", "Account Number", "Mileage")
  
    For Each ws In Worksheets
        If ws.Name <> OutSH.Name And ws.Name <> "ByDept" Then _
            OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 10).Value = _
                ws.Cells(80, 2).Resize(1, 10).Value
    Next ws

OutSH.Range("A:A").ClearContents
OutSH.Columns.AutoFit
Set OutSH = Nothing

End Sub

so where the above code pulls back data from row 80, for example i want it to pull data from row 1 - 100.

If you could please explain how i can modify the above to do that it would be greatly appreciated :D

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Originally Posted by azreil

...how to change the code to include all rows...

Something like this, then? This assumes row1 is titles on every sheet, so it copies all the data from row2 down to the bottom of column A (used rows only)

    For Each ws In Worksheets
        If ws.Name <> OutSH.Name And ws.Name <> "ByDept" Then _
            ws.Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).EntireRow.Copy _
                OutSH.Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next ws