Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Save Worksheet in different workbook? (Excel 2000/2003)

    Hi

    Is it possble to save the current Worksheet into another workbook.

    The reason for asking is I have a workbook that has lots of macro's for creating data in a worksheet, after creating the data for worksheet1

    I would like to save it to new workbook called say (Accumalated), as Sheet1, data values only.

    Then in the Master Workbook I would create worksheet2, then save it to Workbook (Accumalated) as Sheet 2 and so on.

    Is this possible please.

    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Save Worksheet in different workbook? (Excel 2000/2003)

    As an alternative, you could use the "master workbook" to store the macros, and adapt the macros to create the data in a new workbook instead of in the master workbook.
    Or is there a good reason not to do it that way?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Save Worksheet in different workbook? (Excel 2000/2003)

    Hi Hans

    This sounds lik a good solution, but theres is other data to be transferred other than that created by the macro's.

    Thanks for the prompt reply.

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Save Worksheet in different workbook? (Excel 2000/2003)

    See if you can adapt the following code for your needs:

    Dim wshSource As Worksheet
    Dim wbkTarget As Workbook
    Dim wshTarget As Worksheet

    ' Code to fill Sheet1 goes here
    ...

    Set wshSource = ThisWorkbook.Worksheets("Sheet1")
    ' Create new workbook with one worksheet
    Set wbkTarget = Workbooks.Add(xlWBATWorksheet)
    Set wshTarget = wbkTarget.Worksheets(1)
    ' Copy
    wshSource.UsedRange.Copy
    ' Paste special - only values and formats
    wshTarget.Range("A1").PasteSpecial xlPasteValues
    wshTarget.Range("A1").PasteSpecial xlPasteFormats
    ' Optional - delete source
    wshSource.Delete

    ' Code to fill Sheet2 goes here
    ...

    Set wshSource = ThisWorkbook.Worksheets("Sheet2")
    ' Create new worksheet in new workbook
    Set wshTarget = wbkTarget.Worksheets.Add
    ' Copy
    wshSource.UsedRange.Copy
    ' Paste values and formats
    wshTarget.Range("A1").PasteSpecial xlPasteValues
    wshTarget.Range("A1").PasteSpecial xlPasteFormats
    ' Optional - delete source
    wshSource.Delete

    You can repeat the last section (adjusting the sheet name of course) as necessary.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Save Worksheet in different workbook? (Excel 2000/2003)

    Hi Hans

    Thank you very much.

    I will see what I can do with your code.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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