+ Reply to Thread
Results 1 to 7 of 7

Copying one sheet to multiple workbooks

  1. #1
    Registered User
    Join Date
    10-28-2013
    Location
    Belfast, NI
    MS-Off Ver
    Excel 2007
    Posts
    3

    Copying one sheet to multiple workbooks

    Hi,
    I'm new to this forum so hopefully someone can help!

    I have a sheet in an excel document, and I want this sheet to be copied to multiple workbooks (which aren't opened yet).

    All of the workbooks will be in the one folder, but there will be 200+ so it would be too manual to open each one and copy the sheet into each one individually.

    Has anyone got any ideas?

    Thanks,
    Cathal

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copying one sheet to multiple workbooks

    Try this code - change the highlighted sections to match your system and sheetname.
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    10-28-2013
    Location
    Belfast, NI
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Copying one sheet to multiple workbooks

    arlu1201,
    Thanks for that code, it works great! Just one thing is happening, the sheet that I am copying has cells referencing to cells in other sheets within the workbook, but when the template is copied to another workbook, it still references to the original workbook, not the one it's in?

    Any ideas why this would be happening?

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copying one sheet to multiple workbooks

    The sheet is being copied hence it is linked to those other sheets. You can change the links to reference the current file but since you have 200+ workbooks i think its better the code does it. Let me find a way to get that inserted in the code. So the macro will copy the sheet and then change the link in the formulae from the old workbook to the new one. I knew a way to do this in 2003, will find it in 2007 and implement it in the code. I will give you the code shortly.

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copying one sheet to multiple workbooks

    See if this works for you -
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-28-2013
    Location
    Belfast, NI
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Copying one sheet to multiple workbooks

    I am getting a Run-time error '13': Type mismatch on the line:

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Copying one sheet to multiple workbooks

    Can you please attach a sample of few of your files so i can test it?

+ 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. Copying the Same Range from Multiple Workbooks
    By anthony.mcgovern in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-28-2014, 02:09 PM
  2. Copying Multiple worksheets in Multiple workbooks to one single workbook
    By ebrabender in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-28-2011, 02:30 PM
  3. Macro-copying multiple workbooks into master
    By eric.cappelli in forum Excel General
    Replies: 0
    Last Post: 05-08-2009, 08:12 AM
  4. Copying cells To/From multiple Workbooks
    By scadaman29325 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-07-2007, 10:14 AM
  5. Copying from multiple workbooks
    By Rlyeh462 in forum Excel General
    Replies: 5
    Last Post: 04-13-2007, 11:57 AM

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