+ Reply to Thread
Results 1 to 9 of 9

Copy and Paste 3 sheets to 1

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    36

    Copy and Paste 3 sheets to 1

    Hi

    I am building a report for work. I wish to copy and paste data from 3 different sheets into 1 to form a final report. The problem is, each month there will be a different amount of lines to copy across. Is there a way to write a macro that will only copy the cells from row 2 or 3 down but only rows that have text in them?

    also how do I get the lines to copy across to the line below where the last sheets copy ended?

    I hope this makes sense.

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Copy and Paste 3 sheets to 1

    Take a look to this.

    http://excel.tips.net/T003005_Conden..._Into_One.html
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor
    Join Date
    01-17-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    211

    Re: Copy and Paste 3 sheets to 1

    Not the fastest way or best way but ...since you don't want to transfer blank lines....
    also note that it will add some text at end of text for each sheet this is to know when to step out of it.

    Please Login or Register  to view this content.
    If solved remember to mark Thread as solved , to mark your thread as Solved select Thread Tools and click Mark thread as Solved.

    I can't read the mind of my wife so then I get picture but no sound .... and then I mostly get the idea, same goes here picture your outcome and I get the idea.

  4. #4
    Registered User
    Join Date
    04-09-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Copy and Paste 3 sheets to 1

    Hi

    what if my workbook has many sheets? I want the data in sheet named "SC" and the data in sheet "PR" to consolidate into "Rotables"

    Right now it isnt working and just returns "Type" with the dotted line under and then "Type" with another dotted line under.

    Also does it matter if the data is filtered? will it only copy the filtered data or will it copy all the lines?

  5. #5
    Forum Contributor
    Join Date
    01-17-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    211

    Re: Copy and Paste 3 sheets to 1

    Well the fact you mention its filterd i assume you have not only data in column A but also in plenty of other columns
    The code provided only takes from column A see =>
    Please Login or Register  to view this content.
    So then you better go with the tool provided above and dont worry about blank rows being transferd also.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,754

    Re: Copy and Paste 3 sheets to 1

    Maybe see this post:

    Error in VBA


    Please Login or Register  to view this content.

    Regards, TMS

  7. #7
    Registered User
    Join Date
    04-09-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Copy and Paste 3 sheets to 1

    I am struggling to get the consolidation tool to work, i specify the ranges and then click okay and it does nothing?

  8. #8
    Registered User
    Join Date
    04-09-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Copy and Paste 3 sheets to 1

    Hi

    so here is a dummy workbook with my problem.

    There are other tabs in the original worksheet, but these are the ones i need work on.

    The goal is to filter by "Rotable" in "PR" and "SC" and then consolidate all the rotable lines in the "Rotable" sheet. The same must then be done for "Non-Rotables" and then finally to do some more filtering in the "Rotable" and "Non-Rotable" sheets respectively, once the filtering is done, they should be pasted into the "Final" worksheet.

    Preferably these should be written in several Macros, so that small changes can be made in between stages manually.

    Also, there is alot more data than I am revealing here, '000's of lines for each of the SC and PR sheets.
    Attached Files Attached Files

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,754

    Re: Copy and Paste 3 sheets to 1

    OK, try the following code (in a standard module). It will take all the cells on sheets PR and SC and copy them to sheet Final.

    It starts on Row 1 but you can adjust that if necessary.

    Please Login or Register  to view this content.

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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