+ Reply to Thread
Results 1 to 16 of 16

VBA code to pull range of data from multiple cells into a consolidated file

  1. 07-09-2015, 08:40 PM #1
    iblair
    iblair is offline
    Registered User
    Join Date
    07-09-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    9

    VBA code to pull range of data from multiple cells into a consolidated file

    Hi,

    I am very new to VBA and am trying to extract specific data from multiple workbooks all located in the same folder. Each workbook has the same structure with the information required in the same cells of the same sheet.

    Here are the key pieces:
    Destination folder: C:\Users\Public\Iain Blair\PPSR Directory\TEST
    Worksheet in each file with data required: Data
    Cell range sought: A2:G2
    Destination file for collated data: zmaster.xlsm
    Destination sheet: Sheet1

    I copied the below code from a web tutorial but can't get it complete. I receive a Run time error '1004': stating that 'PPSR Template Master -1223.xlsx' could not be found.

    Any assistance greatly appreciated 

    Please Login or Register  to view this content.
    Last edited by alansidman; 07-09-2015 at 08:58 PM. Reason: code tags
    Reply With QuoteRegister To Reply
  2. 07-09-2015, 08:58 PM #2
    alansidman
    alansidman is online now
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,857

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    Code Tags Added
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  3. 07-09-2015, 09:04 PM #3
    iblair
    iblair is offline
    Registered User
    Join Date
    07-09-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    9

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    Thanks Alan,

    Apologies for the error and cheers for correcting.

    Regards

    Iain
    Reply With QuoteRegister To Reply
  4. 07-09-2015, 09:39 PM #4
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    assume you are putting the code into zmaster.xlsm file

    Please Login or Register  to view this content.
    Last edited by humdingaling; 07-09-2015 at 09:42 PM.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  5. 07-09-2015, 09:57 PM #5
    iblair
    iblair is offline
    Registered User
    Join Date
    07-09-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    9

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    Hi. Yes code in the zmaster file. Alter suggested but still receiving same error code of specific file not found.

    Debug takes me to line 
    Please Login or Register  to view this content.
    File mentioned is first in the folder....
    Reply With QuoteRegister To Reply
  6. 07-09-2015, 10:23 PM #6
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA code to pull range of data from multiple cells into a consolidated file 

    Please Login or Register  to view this content.
    try this
    Reply With QuoteRegister To Reply
  7. 07-09-2015, 10:59 PM #7
    iblair
    iblair is offline
    Registered User
    Join Date
    07-09-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    9

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    very close! macro now runs, but then reports following error:

    Run-time error '9':
    subscript out of range

    Debug takes me to:

    [CODE]
    Sheets("Data").Range("A2:G2").Copy Destination:=ThisWorkbook.Worksheets("Sheet1").Range("A" & erow)

    [\CODE]


    Also overwrote some data sitting in cells further down Sheet1 (only 2 files to be read while I build this).....

    thanks again for your help!!
    Reply With QuoteRegister To Reply
  8. 07-09-2015, 11:00 PM #8
    iblair
    iblair is offline
    Registered User
    Join Date
    07-09-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    9

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    very close! macro now runs, but then reports following error:

    Run-time error '9':
    subscript out of range

    Debug takes me to:

    Please Login or Register  to view this content.

    Also overwrote some data sitting in cells further down Sheet1 (only 2 files to be read while I build this).....

    thanks again for your help!!
    Reply With QuoteRegister To Reply
  9. 07-09-2015, 11:01 PM #9
    iblair
    iblair is offline
    Registered User
    Join Date
    07-09-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    9

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    Sorry to clarify, no data has been source and copied...
    Reply With QuoteRegister To Reply
  10. 07-09-2015, 11:05 PM #10
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    did you paste the code into the workbook itself or into your personal.xlsb?
    Reply With QuoteRegister To Reply
  11. 07-09-2015, 11:07 PM #11
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA code to pull range of data from multiple cells into a consolidated file 

    Please Login or Register  to view this content.
    changed the code so it takes the workbook you have active at time of running code instead
    Reply With QuoteRegister To Reply
  12. 07-09-2015, 11:46 PM #12
    iblair
    iblair is offline
    Registered User
    Join Date
    07-09-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    9

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    OK. We are getting somewhere!! :-)

    Below code managed to collate the data from the first rile in the directory, but not the 2nd. At end of running macro, returned the same 'Run-time error '9' code

    What would make the code not grab the data from the 2nd file?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  13. 07-10-2015, 12:13 AM #13
    iblair
    iblair is offline
    Registered User
    Join Date
    07-09-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    9

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    OK. Here is the breakdown:

    Code is extracting data from first file only. MyFile is identified as this first file (1233.xlsx)
    Files in the folder are:

    1233.xlsx
    4337.xlsx
    ZMaster.xlsm

    If I run the macro with code below, I get only data from 1233.xlsx

    The Runtime error '9' appears to be in the loop function as each step prior appears to work.....

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  14. 07-10-2015, 12:25 AM #14
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    i've attached my test script except i used
    Please Login or Register  to view this content.
    instead of the fixed one you have

    i think the error is probably
    Please Login or Register  to view this content.
    i dont have that in my code
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  15. 07-10-2015, 01:27 AM #15
    iblair
    iblair is offline
    Registered User
    Join Date
    07-09-2015
    Location
    Sydney, Australia
    MS-Off Ver
    2007
    Posts
    9

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    Hi Humdingaling,

    OK, I have the macro working, however the resulting data is just off.

    The source cells in the target files are simple copy functions of other cells in the document (this was so I simply had a range to work with for this exercise). What the code is doing however is pulling the next cell in the original cell being copied.

    For example, say source cell in target files are: A2, A3 and A4 ...each is a formula of copy from another cell in another sheet, so the formulas read: =Cover!E14 / =Cover!E18 / =Cover!E22

    when the Maco runs, the first file read is correct, then the 2nd file is returning values from =Cover!E15 / =Cover!E19 / =Cover!E23 and so on.

    Not sure where in the code it is directing to take the next row number from that used in the first file, but that is what I'm getting.

    So close now!! :-)

    Here is the full code being used

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  16. 07-10-2015, 02:11 AM #16
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA code to pull range of data from multiple cells into a consolidated file

    you want to copy and paste values?
    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. Gathering data from multiple excel files into a consolidated file- Possible MACRO??
    By AFIOF719 in forum Excel General
    Replies: 4
    Last Post: 02-12-2014, 06:38 PM
  2. Macro to Copy multiple worksheets into a consolidated file
    By Crossy in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-11-2013, 02:35 AM
  3. [SOLVED] Create consolidated dynamic sheet from several sheets (pull data)
    By G.Bregvadze in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-23-2013, 03:27 PM
  4. Code to pull data from multiple files?
    By WaveOfMutilation in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-17-2010, 07:25 PM
  5. Code to Pull Data from a Different File
    By HRBonne in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-26-2010, 08:40 PM

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