?
Solved

Consolidating Data from Multiple Tables on Multiple WorkSheets

Posted on 2013-10-31
2
Medium Priority
?
212 Views
Last Modified: 2013-11-13
I have several worksheets with data tables that I would like to consolidate on a single worksheet for evaluation.
All data tables are cells B5:U1004 on each worksheet but not all rows have data.
I need a macro that selects all the rows in the table up to the last row in the table that has data and copy it.  Then paste it into the first empty row in the worksheet in which I will be consolidating all the data tables...and do this for each worksheet that has that table.

I've attached a sample workbook with smaller data tables and the last worksheet is the final results I'm looking for.

Thanks,

Edwin
Data-Table-Test-Book.xlsm
0
Comment
Question by:gixxer1020
2 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 2000 total points
ID: 39616000
Give this macro a try:
Sub CopySheets()
Dim LrRet, LrDs, LrMot, LrCd1, LrCd2 As Long

Sheets("RET").Select
  LrRet = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("B6:P" & LrRet).Select
      Selection.Copy
    Sheets("Consolidated Data").Select
  Range("B6").Select
ActiveSheet.Paste
Sheets("DS").Select
  LrDs = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("B6:P" & LrDs).Select
      Application.CutCopyMode = False
        Selection.Copy
      Sheets("Consolidated Data").Select
    LrCd1 = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
  Range("B" & LrCd1).Select
ActiveSheet.Paste
Sheets("MOT").Select
  LrMot = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("B6:P" & LrMot).Select
      Application.CutCopyMode = False
        Selection.Copy
      Sheets("Consolidated Data").Select
    LrCd2 = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
  Range("B" & LrCd2).Select
ActiveSheet.Paste

End Sub

Open in new window

Flyster
0
 

Author Closing Comment

by:gixxer1020
ID: 39647039
Works like a charm, thanks Flyster
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question