Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Output Worksheets to a new Workbook (Excel 2002)

    Hi

    I need to be able to choose any number of worksheets and and create a new workbook but formulas must be transferred as values and not formulas, so that they are not reliant on the master workbook.

    ie. I would like to be able to select (Worksheet names) Input,Installation Request,Installations, and then create new workbook with with these three worksheets in it but containing values and not the formulas.

    As there are lot of worksheets in the workbook it would be nice if the user could choose the worksheets from a list, if feasable

    I hope this is clear.

    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: Output Worksheets to a new Workbook (Excel 2002)

    You could use a userform with a multiselect list box to display the worksheet names. See attached demo. Look at the code behind the userform to see how it is done; i added lots of comments in the code.

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Hi Hans

    Thank you for your prompt reply, I will have a look at your workbook and report back.

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

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Hi Hans

    That's Excellent, Is it possible to not include hidden sheets in the form as I do not want to show the underlying data, and also is it possible to export the formatting?

    The hidden sheets request is the most important.

    Thank you for your assistance in this.

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

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    You can build in a check in the loop that populates the list box in the UserForm_Initialize code to include only worksheets with Visible = xlSheetVisible

    You can paste formats by adding the following below the line that pastes the values:

    ' And formatting too
    wsT.Range("A1").PasteSpecial Paste:=xlPasteFormats

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Hi Hans

    I managed to incorporate the the formatting part of the code, but I am struggling with the
    (You can build in a check in the loop that populates the list box in the UserForm_Initialize code to include only worksheets with Visible = xlSheetVisible)

    Sorry to be so dumb.

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

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    For Each ws In Worksheets
    If ws.Visible = xlSheetVisible Then
    Me.lbxSheets.AddItem ws.Name
    End If
    Next ws
    Set ws = Nothing
    End Sub

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Hi Hans

    You are truly an angel in disguise.

    Many Thanks

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

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Hi Hans

    Just little follow up from this, the colour and number formatting comes across nicely but the column and row widths do not, is there any way to resolve this please.


    Many Thanks

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

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Try the attached version of cmdOK_Click. It copies entire worksheets, then replaces formulas with values, so all formatting should be preserved, including the elusive row widths. [img]/forums/images/smilies/smile.gif[/img]

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Hi Hans

    Thanks for such a prompt reply, tried the macro it errors and highlights the following: wsT.Range("A1").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True.


    Thanks

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

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Have you replaced the entire original version of cmdOK_Click with the new one? The code works without errors for me.

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Hi Hans

    My apologies I forget to say that the errror says, This operation requires merged cells to be identically sized, yes it works if there are no merged cells on the worksheets.

    Many thanks

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

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

    Re: Output Worksheets to a new Workbook (Excel 2002)

    One more good reason not to use merged cells.

    The choice is up to you - use yesterday's version and lose some formatting, or today's version and avoid merged cells.

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Output Worksheets to a new Workbook (Excel 2002)

    Braddy,
    When you say you want to remove all formulae, do you mean that you just want to remove any links to the master workbook or do you specifically want to remove all formulae? If it's the former, then you may be able to adapt Hans' code to copy the sheets to a new workbook, then break any links to the master book rather than having to copy and 'paste as values' all cells.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 3 123 LastLast

Posting Permissions

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