VBA copy worksheets routine

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hans, that’s perfect.
Thank you very much

Reply | Quote