+ Reply to Thread
Results 1 to 13 of 13

Copy a range of cells from multiple worksheets in multiple workbooks

  1. 11-19-2020, 10:56 PM #1
    mclaire
    mclaire is offline
    Registered User
    Join Date
    11-19-2020
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    12

    Copy a range of cells from multiple worksheets in multiple workbooks

    Hello

    I have a macro that allows me to copy a range of cells from a nominated worksheet that exists in several workbooks from a localised folder into one master sheet, see code below. It works really well I especially like being able to change the worksheet names and range of cells as depending on the project I am working on they may vary.

    However I'd like to expand on this macro and instead of updating the worksheet name each time I want to copy data, I'd like to add all the worksheet names into the macro and have the ranges appear on new worksheets in the master (labelled with the name of the worksheet they have been copied from). See example attached (includes codes as well).

    I'd appreciate any help you can give on how to achieve this.

    Claire


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mclaire; 11-23-2020 at 10:54 PM.
    Reply With QuoteRegister To Reply
  2. 11-19-2020, 11:15 PM #2
    jolivanes
    jolivanes is offline
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,513

    Re: Copy a range of cells from multiple worksheets in multiple workbooks

    Looks like you could copy every worksheet in every workbook into your Master. Whichever way you go, you can't have Sheets with the same names.
    Do the Sheets you want to copy from have links to other Sheets like formulas that refer to another Sheet?
    Reply With QuoteRegister To Reply
  3. 11-19-2020, 11:28 PM #3
    mclaire
    mclaire is offline
    Registered User
    Join Date
    11-19-2020
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    12

    Re: Copy a range of cells from multiple worksheets in multiple workbooks

    Hi

    Thanks for getting back to me.

    The current macro I have allows me to copy one set of worksheets from my workbooks at a time i.e. I can copy all the "project_levelA" worksheet data from the four workbooks and they appear on the "Master" worksheet in my macro workbook in numeric order. What I'd like to do is to be able to do this with more than one worksheet so that I don't have to keep editing the worksheet name in the macro each time, I'd like the output to sit on a worksheet labelled with the corresponding name of the worksheet I copied so that I don't mix up my data.

    The worksheets in the workbooks are linked the other sheets within those workbooks by formula. I just want to copy the values in the columns.

    Claire
    Reply With QuoteRegister To Reply
  4. 11-20-2020, 12:01 AM #4
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy a range of cells from multiple worksheets in multiple workbooks

    Try this (not tested).

    The code doesn't check for the existence of the sheets in the source or destination workbooks, and presumes the four Project sheets already exists in the Destination workbook with headers in rows 1:4

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 11-20-2020 at 12:13 AM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.
    Reply With QuoteRegister To Reply
  5. 11-20-2020, 12:21 AM #5
    mclaire
    mclaire is offline
    Registered User
    Join Date
    11-19-2020
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    12

    Re: Copy a range of cells from multiple worksheets in multiple workbooks

    Hi

    Almost there it just needs some tweaks, I got the data in each work sheet however it's going down the page as opposed to across it (the data is just a sample, if my data sets were small it would be fine but they are quite long). Also the file name is missing from top of the data (code for this is in my original post). Thanks again for your help!

    Claire
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 11-20-2020, 12:51 AM #6
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy a range of cells from multiple worksheets in multiple workbooks 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 11-20-2020, 01:05 AM #7
    mclaire
    mclaire is offline
    Registered User
    Join Date
    11-19-2020
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    12

    Re: Copy a range of cells from multiple worksheets in multiple workbooks

    Hi

    Thanks for sending on that code, I'm getting the following error message

    error_message_20201120.JPG
    Reply With QuoteRegister To Reply
  8. 11-20-2020, 01:09 AM #8
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy a range of cells from multiple worksheets in multiple workbooks 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 11-20-2020, 01:35 AM #9
    mclaire
    mclaire is offline
    Registered User
    Join Date
    11-19-2020
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    12

    Re: Copy a range of cells from multiple worksheets in multiple workbooks

    This worked perfectly thank you so much, you're a legend!
    Reply With QuoteRegister To Reply
  10. 11-23-2020, 08:11 PM #10
    mclaire
    mclaire is offline
    Registered User
    Join Date
    11-19-2020
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    12

    Re: Copy a range of cells from multiple worksheets in multiple workbooks

    Hi

    A quick query I forgot to ask the other day, when I tried to edit my macro for my project I changed the Array names, the file location and the number of columns and the cell range.
    I have 22 workbooks in my folder to copy from and when I run the macro the worksheet names appear as per the Array names however no data appears. I also tried changing the col = col+4 to +8 but it didn't make a difference.
    I feel like I've missed something in my updates, any help would be appreciated!


    Please Login or Register  to view this content.
    Last edited by mclaire; 11-23-2020 at 10:54 PM.
    Reply With QuoteRegister To Reply
  11. 11-23-2020, 10:46 PM #11
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy a range of cells from multiple worksheets in multiple workbooks

    Please edit your posts #1 and #10 and put CODE tags around your code. It's a forum rule.

    I'm not sure what the problem is. The code looks correct. Do the 22 workbooks have the exact sheet names as in the array?

    Please Login or Register  to view this content.
    You will need col = col + 8, but that's not why there is no data copied.
    Reply With QuoteRegister To Reply
  12. 11-23-2020, 10:56 PM #12
    mclaire
    mclaire is offline
    Registered User
    Join Date
    11-19-2020
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    12

    Re: Copy a range of cells from multiple worksheets in multiple workbooks

    Hi


    Thanks for getting back to me (have made edits to #1 and #10, I think!) the 22 worksheets have the exact worksheet names as in the array. I'll try again with the col = col + 8.
    Reply With QuoteRegister To Reply
  13. 11-23-2020, 10:58 PM #13
    mclaire
    mclaire is offline
    Registered User
    Join Date
    11-19-2020
    Location
    Adelaide, South Australia
    MS-Off Ver
    Office 365
    Posts
    12

    Re: Copy a range of cells from multiple worksheets in multiple workbooks

    Hey

    I don't know why but it's working now, it's something I did I must has pressed something. Thanks again for your help!

    Claire
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy range of one worksheet from multiple workbooks to multiple worksheets in one master
    By krish2503 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-03-2016, 10:18 AM
  2. Trying to finish a VBA code to copy cells from multiple workbooks and worksheets
    By sweetnasty in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-10-2014, 11:56 PM
  3. [SOLVED] VBA code to copy cell range from all worksheets for multiple workbooks
    By Beany213 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-21-2013, 12:34 PM
  4. Copy multiple values from multiple worksheets from multiple workbooks - with a twist
    By bk34 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 03-13-2013, 04:15 PM
  5. Copy/Paste Range of Data from Multiple Workbooks/Worksheets to Master Workbook/Worksheets
    By NumberCruncher311 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-19-2013, 08:21 PM
  6. [SOLVED] Copy cells from multiple worksheets and workbooks to one master workbook
    By Concept in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2012, 01:42 PM
  7. copy selected range from multiple workbooks into multiple worksheets in one workbook
    By novak100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-13-2012, 05:52 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1