+ Reply to Thread
Results 1 to 4 of 4

One macro to open multiple workbooks and sheets issue

  1. #1
    Registered User
    Join Date
    05-01-2018
    Location
    Maine
    MS-Off Ver
    2010
    Posts
    44

    One macro to open multiple workbooks and sheets issue

    Hello,

    I just wanted to Thank You in advance for reading and possibly helping me. I have some knowledge of VBA but I have issues figuring out the intricacies unless I am modifying an already existing code. I am learning.

    I have been trying to get a macro that will open a different workbook, find the sheet, insert a blank range, copy data from row above and paste it is a value in the newly created range. This macro will need to repeat this in about 11 workbooks with multiple sheets in each. I have attempted to do this with macro recorder and creating a few objects but I am running out of luck when it attempts to switch to the next workbook and change sheets. Above that... I know there must be a better way as this seems to be very manual and the code will be super long when its done.

    I have included a copy of a mock up for what I am attempting. It consists of 3 workbooks. The main workbook has the macro and the other two will be the ones modified. Again, it will need to do this with about 11 workbooks in the end. Changing the VBA script to the location you saved them should work and you can see what my problem is.

    Any help at all would be very very appreciated. I am learning but I am still using macro recorder as my primary means which is probably the issue in itself.

    Thank you for reading.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

    Re: One macro to open multiple workbooks and sheets issue

    Are the filenames of the 11 workbooks known and constant, or do you want to loop through all files in a folder, or look for file names with a base name and number e.g. File01.xlsx, File02.xlsx...File11.xlsx, or prompt the user to select the files ?

    Same for the worksheets in each file. Do you want to loop through all sheets, or a list of specific ones, or ones that have a base name?

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 10-17-2019 at 07:11 PM.
    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.

  3. #3
    Registered User
    Join Date
    05-01-2018
    Location
    Maine
    MS-Off Ver
    2010
    Posts
    44

    Re: One macro to open multiple workbooks and sheets issue

    Thank you for sending this. The sheets are based off of the names, essentially the first sheet does not need to be included. No reason for that code to be reworked though. It works super fast and I should have no problem making it work. Thank you so much. You saved me tons of frustration and time with a much better code.

    I do have a question though just for my own understanding of VBA. "strPath = ThisWorkbook.Path & "\"" What does this actually mean? Is this a way to define a workbook that will be referred to multiple times? What does the "\" represent?

    I get confused on what a string represents and how to define them properly. Thank you again for the assistance in the code. I am impressed and it is extremely helpful for me.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

    Re: One macro to open multiple workbooks and sheets issue

    You're welcome.

    ThisWorkbook is a keyword that references the workbook that this code is within.

    ThisWorkbook.Path returns the path (folder) that ThisWorkbook is in. I assumed all the other workbooks were in the same folder e.g.
    C:\MyFolder\MySubfolder

    Adding a path separator "\"at the end of the path allows you to add a file name at the end which the code later does.
    C:\MyFolder\MySubfolder\Filename.xlsx

    A string is programming-speak meaning text. I think the etymology comes from some math term; a string of symbols.

+ 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. Need Macro that copies Sheets to 3 different open excel workbooks and saves it
    By Godfrey8 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-12-2019, 11:03 AM
  2. 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
  3. [SOLVED] Macro to open multiple workbooks and delete specific sheets
    By JPSIMMON in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-03-2015, 07:39 PM
  4. [SOLVED] how to use macro (split sheets/tab) to all open workbooks
    By 2977cc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-23-2015, 11:43 AM
  5. Application.Ontime issue running macros in wrong spreadsheet with multiple sheets open
    By aaron.irvine in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-02-2014, 09:42 PM
  6. [SOLVED] Run Macro on Multiple OPEN Workbooks
    By DrEcosse in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2012, 09:22 PM
  7. macro to open multiple workbooks at once
    By easty in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-30-2010, 08:48 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