+ Reply to Thread
Results 1 to 3 of 3

Copying multiple ranges from multiple worksheets and pasting to one spot

  1. #1
    Registered User
    Join Date
    10-12-2007
    Posts
    2

    Copying multiple ranges from multiple worksheets and pasting to one spot

    Hi there. I'm having this problem with data that is scattered across several excel worksheets. There is lots of it. On the worksheet where I do all my calculations, i have no problem using built in formulas like average or stdev with the resulting big nasty range, like so:

    Please Login or Register  to view this content.
    but I need to find a way to copy all values from all of those ranges and paste them into one spot.

    ie: could put that range into a macro and have it copy all of the referenced cells and paste all the values into one column.

    Is this possible, or am I going to have to copy and paste one by one all of the data from each worksheet to a single place? (there are lots of data points).

    Thanks a lot!
    Kris

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    If you are working with a range spread out like that, you might consider redesinging the spreadsheet.

    If that's not possible, a Named Range might be the easiest way to go.

    For the VB route, I wouldn't use the combined discontinous range. I'm never sure when I can use a discontinous range in one piece and when I have to loop through the Areas of a discontinous range.

  3. #3
    Registered User
    Join Date
    10-12-2007
    Posts
    2

    Smile

    Thanks for your reply.

    Well, what if all the ranges were on one worksheet? I've cut and pasted them all into one worksheet, now i just need to be able to paste a discontinuous range into an inputbox and have the macro copy all the data from the range that I paste in into one column.

    I've tried a multiple copy macro that I found floating around, but it pastes all my values into cells all over the place (ie: relative to where they were before) instead of all in one column.

    I realize this is probably a really simple question for somebody who knows VBA well!!
    Thanks!

+ 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