+ Reply to Thread
Results 1 to 5 of 5

VBA: Fetch workbooks from file path; paste to sheets in active workbook

  1. #1
    Registered User
    Join Date
    03-27-2017
    Location
    Dubai
    MS-Off Ver
    Excel 2013
    Posts
    18

    VBA: Fetch workbooks from file path; paste to sheets in active workbook

    Good afternoon,

    I am hoping that someone can offer assistance writing a VBA command. The command aims to look through workbooks in a file path directory, find any workbooks that have a predefined numeric prefix, and copy their contents to tabs in an active workbook.

    The active workbook will have a command tab and eight data tabs. Each data tab needs to be populated by data in an Excel workbook (all data workbooks have only one tab), saved in a file directory on my computer.

    The numeric prefix, "12345" for example, will be dynamic and typed in to a cell in the control tab. There will be eight separate workbooks with each unique prefix in the file directory to be found and copied.

    For example, using the numeric prefix 12345, the eight files would be called:

    12345_AttributionCountry_GRPS.xlsx
    12345_AttributionSector_GRPS.xlsx
    12345_CharacteristicsRisk.EXP.xlsx
    12345_CountryWeights.CLPSE.xlsx
    12345_SectorWeights.CLPSE.xlsx
    12345_TopBottomContributors.EXP.xlsx
    12345_ValuationDetail.EXP.xlsx
    12345_TradeActivity.EXP.xlsx

    The contents of each of these should be copied in to the eight data tabs in the active workbook, with the tabs named in line with the above but without the numeric prefix (e.g. the first tab would be called "AttributionCountty_GRPS").

    If anyone can help out it would be really appreciated.

    Many thanks
    Last edited by ahazeldene; 02-04-2019 at 07:24 AM.

  2. #2
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: VBA: Fetch workbooks from file path; paste to sheets in active workbook

    What about using "Power Query" addin?
    Teach me Excel VBA

  3. #3
    Registered User
    Join Date
    03-27-2017
    Location
    Dubai
    MS-Off Ver
    Excel 2013
    Posts
    18

    Re: VBA: Fetch workbooks from file path; paste to sheets in active workbook

    Hi,

    Thanks for the thought, would rather use VBA if possible. Does anyone have any ideas?

  4. #4
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    199

    Re: VBA: Fetch workbooks from file path; paste to sheets in active workbook

    Could you attach your workbooks? Would be easier to give this a try
    Tuomas "Banaanas" Savonius
    Trying to give back now when I actually can do some VBA

  5. #5
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: VBA: Fetch workbooks from file path; paste to sheets in active workbook

    Since we don't have your sample workbooks, please see if the attached helps you. But as banaanas said a sample folder containing your source workbooks will help us to help you.

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. vba to copy all sheets in the workbooks open by macro to active workbook
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2018, 12:48 PM
  2. Copy only active sheet 1 and paste into other workbook after or before n sheets
    By FUN2BALA in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-08-2018, 12:51 PM
  3. [SOLVED] copy active workbooks to a paste to closed consolidation workbook
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 05-01-2017, 01:22 PM
  4. Combine several workbooks containing data in multiple sheets into a master Workbook
    By sunrize9 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2014, 09:10 PM
  5. VBA macro for hyperlink to active workbook in the active workbook path
    By Scott Taylor in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-10-2013, 05:37 AM
  6. [SOLVED] Open Multiple Workbooks, Record names, Copy paste to Active Workbook, Close the Workbooks
    By vba_madness in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2013, 06:09 AM
  7. Replies: 2
    Last Post: 06-20-2012, 09:08 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