+ Reply to Thread
Results 1 to 4 of 4

Copy range from multiple sheets with same name in files in same folder to single sheet

  1. #1
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague
    MS-Off Ver
    Excel 2013
    Posts
    161

    Copy range from multiple sheets with same name in files in same folder to single sheet

    Hi all,

    I have problem with a VBA code. I bet it’s a stupid mistake I’m doing but cannot figure it out.
    I have folder with many excel files (Document Folder). In each of the excel files I have sheets, and one of them is named “Orders”.
    I need to copy the values (e.g. range A1:X100) from the sheet “Orders” from all of the excel files in a single “MasterData” sheet.
    In the column “A” of the “MasterData” sheet I need to have the excel file name.
    In columns C and further right I need to have the data from sheet “Orders”, ideally just a text values (I don’t need to see the formulas in the copied cells, rather the result). It will be really great if I could have also the formatting for the copied cells.

    I’ve made a code that is not working as I wish. Can anyone help me figuring out where my mistake is.

    Please Login or Register  to view this content.
    Thank you in advance.
    Igor

  2. #2
    Forum Contributor
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    365
    Posts
    199

    Re: Copy range from multiple sheets with same name in files in same folder to single sheet

    Does this fit your needs?

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague
    MS-Off Ver
    Excel 2013
    Posts
    161

    Re: Copy range from multiple sheets with same name in files in same folder to single sheet

    Hi Banaanas,

    Thank you a lot for your help. The copying works!!!

    The only thing is that it overwriting the copied text. I've made a small change to copy each file below the previous one. I've made small change in the code to copy 50 rows after the previous one. I tis not ideal solution, but for the task i need it will do.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-13-2017
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    5

    Re: Copy range from multiple sheets with same name in files in same folder to single sheet

    its not working for me.
    I got " Run time error "9":
    Subscript out of range

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Consolidate/Copy Data from Multiple Sheets from a Fixed Range into a single sheet
    By Rompurr in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 01-29-2020, 10:53 PM
  2. [SOLVED] copy/paste from multiple files in a folder to single workbook
    By HXIO in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2019, 01:57 PM
  3. Replies: 0
    Last Post: 10-12-2015, 10:02 AM
  4. Replies: 0
    Last Post: 03-24-2015, 03:56 PM
  5. Marcto to copy sheets from one workbook to a master sheet, for all files in folder
    By crombieguy91 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2013, 11:12 AM
  6. excel macro to copy data from multi text files in a folder to a single work sheet
    By dil_se in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2012, 08:10 AM
  7. [SOLVED] Copy range from multiple files in multiple folders to single sheet in master WB
    By Royzer in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-18-2012, 03:40 PM

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