?
Solved

Consolidating Data from Multiple Tables on Multiple WorkSheets

Posted on 2013-10-31
2
Medium Priority
?
214 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

569 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