Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2009
    Location
    Essex UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the following code in a workbook that I have inherited.

    Sub CreateCopies()
    'Copies sheets as required by button on Summary sheet
    Sheets(Array("Assumptions", "Summary Page")).Copy
    End Sub


    The code copies the sheets to another blank workbook, but retains the locked cells and formulae.
    I would like the export to copy the sheets as values only, without any locked cells.

    Also, one other quick question I have is, 'how this code can actually work'? becasue I would have expected after the Copy command that there would need to be a Paste command to the new worksheet? The code pastes to a new workbook without seemingly being told?

    many thanks for any help

    Rob

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If there are no passwords associated with the sheets you can use:
    Code:
    Sub CreateCopies()
    	  'Copies sheets as required by button on Summary sheet
    	  Sheets(Array("Assumptions", "Summary Page")).Copy
    	  Sheets("Assumptions").Unprotect
    	  Sheets("Summary Page").Unprotect
    End Sub

    Part two on how it works. It is an implied paste. Excel knows that you don't want to make a copy of the sheets, not hold the sheets in memory like when selecting the entire sheet and copying the sheet in that manner.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    To answer your second question first: the Copy method for worksheets works differently than the Copy method for ranges (cells).
    It creates a duplicate of a worksheet or worksheets, just like the Edit | Move or copy sheets menu item, of which it is the equivalent.

    You could add code to the existing macro to unlock cells and replace formulas with values:

    Code:
    Sub CreateCopies()
      Dim wsh As Worksheet
      'Copies sheets as required by button on Summary sheet
      Sheets(Array("Assumptions", "Summary Page")).Copy
      ' Loop through the sheets in the new workbook
      For Each wsh In ActiveWorkbook.Worksheets
    	wsh.Unprotect
    	With wsh.UsedRange
    	  .Locked = False
    	  .Value = .Value
    	End With
      Next wsh
    End Sub

  4. #4
    New Lounger
    Join Date
    Oct 2009
    Location
    Essex UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the replies Mbarron and HansV

    I tried HansV solution but there is vba code in the background of the copied worksheets.
    The macro stalls and I get a msgbox that pops up saying

    "Compile error :-
    Sub or Function not defined"

    There are also some radio buttons and Option boxes on the sheet that I am copying. I am happy for these buttons to lose their functionality. It is just important to get the data to another worksheet and retain the sheet formatting ro easy readability.

    Thanks for all your help

    Rob

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does this do what you want?

    Code:
    Sub CreateCopies()
      Dim wbkIn As Workbook
      Dim wbkOut As Workbook
      Dim wsh As Worksheet
      Set wbkIn = ActiveWorkbook
      Set wbkOut = Workbooks.Add(xlWBATWorksheet)
      Set wsh = wbkOut.Worksheets(1)
      wsh.Name = "Assumptions"
      wbkIn.Worksheets("Assumptions").UsedRange.Copy
      wsh.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
      Set wsh = wbkOut.Worksheets.Add(After:=wsh)
      wsh.Name = "Summary Page"
      wbkIn.Worksheets("Summary Page").UsedRange.Copy
      wsh.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    End Sub

  6. #6
    New Lounger
    Join Date
    Oct 2009
    Location
    Essex UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,

    This works great except that, for some reason, I still lose the formatting.
    The locked cells have been removed and Values have replaced formulae, but there is no formatting at all, and the column widths are unadjusted.

    If it is a consideration, I am using excel 2007, but I do need this to work for excel 2003 as well.
    Your help is really appreciated.

    Many thanks

    Rob

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is a version that also copies formats and column widths:

    Code:
    Sub CreateCopies()
      Dim wbkIn As Workbook
      Dim wbkOut As Workbook
      Dim wsh As Worksheet
      Set wbkIn = ActiveWorkbook
      Set wbkOut = Workbooks.Add(xlWBATWorksheet)
      Set wsh = wbkOut.Worksheets(1)
      wsh.Name = "Assumptions"
      wbkIn.Worksheets("Assumptions").UsedRange.Copy
      wsh.Range("A1").PasteSpecial Paste:=xlPasteValues
      wsh.Range("A1").PasteSpecial Paste:=xlPasteFormats
      wsh.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
      Set wsh = wbkOut.Worksheets.Add(After:=wsh)
      wsh.Name = "Summary Page"
      wbkIn.Worksheets("Summary Page").UsedRange.Copy
      wsh.Range("A1").PasteSpecial Paste:=xlPasteValues
      wsh.Range("A1").PasteSpecial Paste:=xlPasteFormats
      wsh.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
    End Sub

  8. #8
    New Lounger
    Join Date
    Oct 2009
    Location
    Essex UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, that's perfect.
    Thank you very much

Posting Permissions

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