+ Reply to Thread
Results 1 to 5 of 5

VBA Copy from sequential workbooks into a single workbook with sequential columns

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    4

    Cool VBA Copy from sequential workbooks into a single workbook with sequential columns

    Hello

    I have 52 workbooks that are going to be updated often. In each of them, I have 2 columns that need to be moved over into a single common workbook. The starting cell for the data is in the same location in all 52 workbooks. It will be 2 columns wide from the starting cell to the right and then down a dynamic amount of rows. It will then need to be placed in the appropriate columns in the common workbook (2 columns from each of the 52 files).

    The files are named consecutively "XXXXX_Ch##_XXX_XXX" where ## represents the number 1 through 52 (note single digit numbers do not have 2 digits in the file name).

    Is there some loop I can do where there is a variable in the name of the other files for the number so I don't have to copy the code 52 times and change the ## however many more times? Also, in this loop, I will need to add 2 to the position in the common workbook for columns as each of the files needs 2 columns pasted in.

    Thank you anyone and everyone for any help!

    Cheers

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: VBA Copy from sequential workbooks into a single workbook with sequential columns

    Simplest method is to leverage PowerQuery, which is free add-in for Excel 2010.

    For VBA you can use variable to increment the number. Though if you want more detailed help, I'd suggest uploading sample files (both the source and output). Preferably 2 source files.

    Ex:
    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    09-13-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: VBA Copy from sequential workbooks into a single workbook with sequential columns

    Wow! Thanks for the quick reply CK!

    This looks like it will work fantastically... I have just a couple of easy for you questions ...

    How do I get excel to open a file based on the variable path and variable file name?

    Do I even need to open the file to copy contents from it?

    Thanks again!

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: VBA Copy from sequential workbooks into a single workbook with sequential columns

    Depends on method. For simple copy paste, yes you need to open file.

    Something like below to open file.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-13-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: VBA Copy from sequential workbooks into a single workbook with sequential columns

    Wow! You are the best

    I can't thank you enough for your speedy replies and time!

+ 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] copy sequential columns / paste to non-sequential columns
    By macrorookie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2014, 04:59 PM
  2. Replies: 3
    Last Post: 01-30-2014, 05:31 AM
  3. Copy non-sequential columns with headers (from multiple sheets) to one consolidated sheet
    By constantmallee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2012, 02:24 PM
  4. [SOLVED] Create sequential letters (A, B, C) in sequential cells
    By Theale in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 08-29-2012, 02:47 PM
  5. How to copy sequential vales into non sequential rows
    By dchalem in forum Excel General
    Replies: 11
    Last Post: 01-18-2011, 12:19 PM
  6. Plotting sequential data against sequential dates
    By Morph71 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-04-2009, 05:33 PM
  7. [SOLVED] Non-sequential VLOOKUP function -OR- sequential sort of web query
    By Eric S in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2005, 04:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1