+ Reply to Thread
Results 1 to 19 of 19

VBA to copy data from multiple sheets side-by-side into one sheet matching date

  1. #1
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    9

    VBA to copy data from multiple sheets side-by-side into one sheet matching date

    I'm really struggling with this one (pretty new to VBA).

    I have a workbook that pulls financial data for a range of dates and places each company's information into a separate worksheet. I'm trying to figure out a way to copy the resulting data from those tabs into one master tab named "Results." The format for each of the source worksheets are all the same.

    I have a script that is successfully pulling the data from all of the worksheets into one worksheet, but it is stacking them all one on top of the other in the same columns (i.e. A1:G), when what I really need is for each to be side-by-side in successive columns for comparison and analysis.

    The next problem is that not all of the worksheets have the full range of dates, so I need to figure out a way to have the destination of the pasted data to correspond with the correct date for that row (either already entered in Column A of the "Results" worksheet or, preferably, based on the worksheet with the most complete set of dates).

    I could do all of this with formulas, but given the number of sheets I'm working with it slows things down way too much.

    Here's the code I've been working with:

    Sub move_data()
    Dim Sht As Worksheet
    Dim Rng As Range
    For Each Sht In Sheets
    If Sht.Name = "Results" Or Sht.Name = "Parameters" Then
    Else
    Set Rng = Sht.Range("A1:G" & Sht.Range("G" & Rows.Count).End(xlUp).Row)
    Rng.Copy Destination:=Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(0, 0)
    End If
    Next Sht
    With Sheets("Results").Range("A1:G" & Range("G" & Rows.Count).End(xlToRight).Row)
    End With
    End Sub



    If there's a better way of doing it, I have no problem scrapping this code for another.

    Would really appreciate any help.
    Last edited by MHCapcog; 04-02-2013 at 04:09 PM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    why not add the worksheetname in one column, then you don't need the information side by side.

    a pivot table can help you with analysing your data.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Oeldere, thanks for the quick response.

    I'd thought about using a pivot table, however, I'm not sure the results would give me what I really need. Basically, I have a separate document that has already been put together which references the data that would be produced by this new workbook which gathers the source data and (hopefully) assembles that data in a way that could be quickly cut/paste into the other workbook for additional analysis.

    The start date of the date range will always be the same, as will the number and names of the worksheets. The last date would change since this workbook would add new data each day.

  4. #4
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Document.xlsm

    Here's a version of the file that I'm working with for reference (though the original has many more worksheets). The Macro has not been run yet in order to save space on the file.

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Currently, you code is copying in to next empty row,so you want the next sheet to be copied in to next column, ie column H in result sheet. It can be done

    but I am not sure by these statement

    The next problem is that not all of the worksheets have the full range of dates, so I need to figure out a way to have the destination of the pasted data to correspond with the correct date for that row (either already entered in Column A of the "Results" worksheet or, preferably, based on the worksheet with the most complete set of dates).

    You need to explain what do you mean by dates and what else do you want to do with them?

  6. #6
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Thanks AB33.

    Regarding your first point, how would I edit the script to do this (again, I'm very new to VBA)?

    As for the dates, the workbook that I have is set up to retrieve data for a particular range of dates (i.e. 1/1/2005 - Today) for a specific set of companies. Not all of the companies have data for the full length of the term (some might not have existed). So, I end up with data that begins with the first date available in the range that has been requested for each company. What I need is basically for Column A of "Results" to include all dates in within the range (which would not be all calendar dates, just trading days - though at least one of the tabs would have all of the dates within the range). If a particular worksheet does not have the full set of dates, then I need it to copy the appropriate data to for the dates that it does have in a way that aligns with the dates of all companies being compared on the "Results" worksheet.

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    You could use this code to do the first one, but I am not sure about the second one

    Please Login or Register  to view this content.
    Last edited by AB33; 04-02-2013 at 05:25 PM.

  8. #8
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Receiving an error: "Invalid use of property"

    with the Application at the end indicated.

  9. #9
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Never mind about that error, got it resolved.

    Thanks for putting this together. That definitely helps.

    Now if I can just get it the rest of the way there.

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Added a header. See above

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    I add my code in the attached file.

    It's called integrated oeldere()

    after that you can use pivot table for analysing.

    Since the file is to big I only put the code below.

    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    oeldere,
    Do you understand the OP's second request, I could not?

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    @AB33

    I think he / she wants to compare the data of the different sheets (for the same range e.g. 1/1/2005 - 31-05-2009).

    (but i could be wrong), so I'm sure the OP (topic starter) will explain better.

    So I suggest to copy all the data of the sheets in one consolidation sheet and after that use this sheet for analysing.

    In that case you have all data in one sheet.

  14. #14
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Here's a better example of what I'm looking for.

    Again, thanks so much for your help.

    Example.xlsx

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    I thought you meant something like this.

    But did you tried the vertical setup?

    It's much easier to analyse with an pivot table (than your format of data).

  16. #16
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Ordinarily, using a pivot table would be fine, but it would be much more compatible with a second workbook that I am working with if it were formatted as described above. I realize that this makes things more difficult, but I'm hoping there might be a solution.

  17. #17
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Then please explain why you want 2 workbooks, for analysing the same information?

    I suggest to use just one.

    Why => less change on failure / it's easier to handle

  18. #18
    Registered User
    Join Date
    04-02-2013
    Location
    Texas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Thanks for your patience.

    There are a couple of reasons. For one thing the other workbook is already built out. The workbook I'm currently working on is being used just to gather and sort the data.

    Second, and perhaps more importantly, the other workbook has a whole lot more going on in it. It isn't just used for basic comparisons, but for indexing, graphing, identifying outliers, etc., and trying to bring this workbook into that one would simply overload it and (given the relatively limited strength of my computer) prevent it from being able to do anything it currently does without crashing.

    I was hoping to keep this one separate so that it is simple, clean and gives me the output that I need, the way I need it for application in the other, more complicated workbook.

  19. #19
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: VBA to copy data from multiple sheets side-by-side into one sheet matching date

    Please Login or Register  to view this content.
    To my opion thats no reason.
    If you get an broken bike, you somethings better buy another one, than repair the broken bike (which will be more expensive)

    For the second one, if you will use bigger files more often you could (should) fix that problem.

    After the fix you can add this information in another sheet in that workbook.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1