Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can templates be in the same wkbook w/ master data (Excel 2003)

    Hi folks
    I have a sheet "master copy" and then templates "sheet 1", "sheet 2" and sheet3" in the same workbook. Now, how can I copy data (say Range 1, 2 & 3) from "master copy" and save as new workbook (at different location then original workbook) based on these templates i.e "sheet1", "sheet2" and "sheet3"?? I want to keep all the templates together along with master copy, if possible.

    Any help would be greatly appreciated. Thanks
    Siz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Can templates be in the same wkbook w/ master data (Excel 2003)

    I'm afraid I don;t understand your description. Can you explain in more detail what you want to do, for example, do you want to create one new workbook, or three new workbooks, or something else? What is the relation between the master copy and the template sheets? Etc.

  3. #3
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can templates be in the same wkbook w/ master data (Excel 2003)

    Dear Hans,
    The idea is to have all information about the project in this master copy sheet including (but not limited to) task descriptions, key notes, timing, assignments etc. I want to generate 3 new files using 3 different sets of info from the master copy, say file 1 would have just task descriptions and key notes. This file 1 will be saved under different directory and folks on the production line would have access to it. Similarly one group would be presented some other combination of data in the form of file 2 and so on. Now I want these 3 new files (file 1, file 2 and file 3) be created based on 3 different templates (sheet1, sheet2 and sheet3) that are in the same workbook along with master copy sheet. So, it is like copying data from master copy and pasting in desired template, then save this template as a new workbook in a new folder. However, I don't want any data left in any of the templates after the whole operation is completed. I was thinking about using a separate command button on the master copy to generate each of these files.

    Lets see if I got my point across, if not, I will be happy to give it another try.

    Thanks for the help.
    Siz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Can templates be in the same wkbook w/ master data (Excel 2003)

    You can copy a "template" worksheet to a new workbook first, using Worksheets("TemplateSheet1").Copy, then copy the information from the master data sheet into the new workbook. Because you're working with more than one open workbook now, it is important to keep track of which is which.
    <code>
    Dim wbkCur As Workbook
    Dim wbkNew As Workbook

    Set wbkCur = ThisWorkbook

    wbkCur.Worksheets("TemplateSheet1").Copy
    Set wbkNew = ActiveWorkbook
    wbkCur.Worksheets("MasterSheet").Range("A1:F100"). Copy _
    Destination:=wbkNew.Worksheets(1).Range("A1")
    wbkNew.Worksheets(1).Name = "AnyName1"
    wbkNew.Close SaveChanges:=True, FileName:="F:Excel1Data1.xls"

    wbkCur.Worksheets("TemplateSheet2").Copy
    Set wbkNew = ActiveWorkbook
    wbkCur.Worksheets("MasterSheet").Range("A1:F100"). Copy _
    Destination:=wbkNew.Worksheets(1).Range("A1")
    wbkNew.Worksheets(1).Name = "AnyName2"
    wbkNew.Close SaveChanges:=True, FileName:="F:Excel2Data2.xls"

    ...
    </code>
    Of course, you must substitute the appropriate names and ranges in the above code, and adapt/expand it as needed.

  5. #5
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can templates be in the same wkbook w/ master data (Excel 2003)

    I have tried the code, doing exactly what I wanted. Thank you!!
    Siz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •