+ Reply to Thread
Results 1 to 3 of 3

Copy Worksheets 1,2 and defined part of 3 to new workbook and save with defined name

  1. #1
    Registered User
    Join Date
    07-31-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    34

    Copy Worksheets 1,2 and defined part of 3 to new workbook and save with defined name

    Hi all,

    I have a current workbookCopy of ASP Test RC File.xlsx with 3 worksheets.

    Worksheet RC is a summary of all companies rates (Columns G:J). I've shortened this to keep the file small but in reality there are actually another 140 columns after Column G.

    What I am looking to do is create an individual file for each Company i.e. each column from G onwards yet keep Columns A:F as these will be the same for all.

    Furthermore, I would like each new file contain the Instructions and Cover Page tabs (Sheet 1 & 2).

    Each new file should be renamed according to the name listed in G5, G6, G7, etc...

    Is there a way to do this with VBA to save me manually having to create 140+ plus files and copying and pasting sheets 1 & 2 into each file?

    Any assistance would be greatly appreciated.

    Kind regards,

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,577

    Re: Copy Worksheets 1,2 and defined part of 3 to new workbook and save with defined name

    A macro similar to this shall do the work for you:
    Please Login or Register  to view this content.
    Provided company names are unique (in the sample file you have TWO Companies C) and are valid as filename - for instance do not have characters like ? * < > [ ] : |

  3. #3
    Registered User
    Join Date
    07-31-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Copy Worksheets 1,2 and defined part of 3 to new workbook and save with defined name

    Hi Kaper,

    Appreciate the response and the code and hoping you have the time to help me tweak it a bit.

    The filename for each new file will actually be based upon a combination of two cells - "G5" - "G7" and then needs to be saved to a specific folder in C:\Users\master\Documents\SAP.

    I think the code will work fine it's just that I am running into to duplicate file name issues due to non-unique Company names i.e. Cell G5,6,7,8 are all for Company A, however, cells G7,8,9,10 have different names so by including these in the naming convention it should negate the issue I seem to be having.

    Looking forward to your response.

    Kind regards.

+ 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. Reopen workbook application-defined or object-defined error
    By Duzze5 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-02-2013, 11:21 AM
  2. ActiveWorkbook.Worksheets.Name - Apllication-defined or object-defined error
    By CLIOWIWI in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-17-2013, 11:19 AM
  3. [SOLVED] Run Time 1004 - App Defined or Object defined error with String defined path structure
    By sarails in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-11-2012, 09:38 PM
  4. copy method failed:application-defined or object-defined error'
    By syam1919 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2005, 05:05 AM
  5. [SOLVED] Application-defined or object-defined error on copy
    By Josh Sale in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-03-2005, 02: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