AndrewMcLaughlin
asked on
Macro to copy+paste+special values all sheets in workbook
Hi,
I've created a spreadsheet template that is going to be used to create a report - as the last stage I need a macro that will:
1) Unprotect the workbook and all worksheets - the password for all of them is the same
2) Copy all cells in all sheets in the workbook and paste the values over them, i.e.,, remove the formulae.
3) Delete two sheets, one called DATA and one called MAPPING
4) Remove the macro altogether so that when a receipient open the report they do not get the message 'this spreadsheet contains macros etc'
Is this achieveable in Excel 2010 - I am not sure if Step 4 is realistic.
Thanks very much,
Andrew
I've created a spreadsheet template that is going to be used to create a report - as the last stage I need a macro that will:
1) Unprotect the workbook and all worksheets - the password for all of them is the same
2) Copy all cells in all sheets in the workbook and paste the values over them, i.e.,, remove the formulae.
3) Delete two sheets, one called DATA and one called MAPPING
4) Remove the macro altogether so that when a receipient open the report they do not get the message 'this spreadsheet contains macros etc'
Is this achieveable in Excel 2010 - I am not sure if Step 4 is realistic.
Thanks very much,
Andrew
I am not sure if step four is realistic either, I think a save as .xlsx would work that would save a copy with no macros.
ASKER
mmmmm......I thought so
what about steps 1-3
what about steps 1-3
Step one is basically: loop through all sheets and use the Unprotect method. Step two is copy the cells values as new value (this overwrites the formula). Step three are two delete instructions. Step four is, as stated above, a save as in xlsx format.
Template:
(°v°)
Template:
Dim wks As Worksheet
With ThisWorkbook
For Each wks In .Worksheets
wks.Unprotect "secret"
With wks.UsedRange
.Value = .Value
End With
Next wks
.Worksheets("DATA").Delete
.Worksheets("MAPPING").Delete
.SaveAs .Path & "\" & Replace(.Name, ".xlsm", ".xlsx"), xlOpenXMLWorkbook
End With
Cheers!(°v°)
Here is a workbook that will do it.
Remove-Formulas.xlsm
Remove-Formulas.xlsm
ASKER
Hi,
Thanks for this - the code takes a very long time to execute and I had to break out of it..
Let me try again tomorrow..i think that maybe disabling the autocalculate on excel may be needed...I use a lot of complex array formulae and I think that every time the copy paste special is done means these have to update - which takes a lot of time.
Thanks,
Andrew
Thanks for this - the code takes a very long time to execute and I had to break out of it..
Let me try again tomorrow..i think that maybe disabling the autocalculate on excel may be needed...I use a lot of complex array formulae and I think that every time the copy paste special is done means these have to update - which takes a lot of time.
Thanks,
Andrew
Which version did you try? If you use peetjh's code, you could use .SpecialCells to loop only through cells with formulas. Depending on your ratio of formulas versus constants, it might be faster to work with the entire UsedRange at once (my code sample) or to work only with formulas, one at a time.
Turning off calculations is a good idea, of course, and once you have proper error handling in place, so is turning off screen updating.
(°v°)
Turning off calculations is a good idea, of course, and once you have proper error handling in place, so is turning off screen updating.
(°v°)
How about this?
Sub test()
Dim wb As Workbook
Dim ws As Worksheet
Dim rngFormulas As Range
Set wb = ThisWorkbook
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In wb.Worksheets
ws.Unprotect "xxxx"
On Error Resume Next
Set rngFormulas = ws.Cells.SpecialCells(xlCellTypeFormulas)
rngFormulas.Value = rngFormulas.Value
Next ws
wb.Worksheets("Data").Delete
wb.Worksheets("Mapping").Delete
wb.Worksheets.Copy
End Sub
ASKER
Hi,
This works - except that instead of copying and pasting the values what I have in the new workbook which is created is the value in cell A1 showing in all the cells.
Does it matter in which order the sheets are copied and pasted over with values?
Is it possible that the code can select sheets sheet1, sheet2 and sheet3 and do those first before doing the others?
Thanks,
Andrew
This works - except that instead of copying and pasting the values what I have in the new workbook which is created is the value in cell A1 showing in all the cells.
Does it matter in which order the sheets are copied and pasted over with values?
Is it possible that the code can select sheets sheet1, sheet2 and sheet3 and do those first before doing the others?
Thanks,
Andrew
ASKER
Hi,
I think something is wrong with the selection of the range here - could it not just be the whole sheet that is selected and then copy with the values pasted back over?
Thanks,
Andrew
I think something is wrong with the selection of the range here - could it not just be the whole sheet that is selected and then copy with the values pasted back over?
Thanks,
Andrew
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great, this works, points are yours - thank you so much
One final thing - how do i disable the pop up with says ' data may exist in the sheets you want to delete'
One final thing - how do i disable the pop up with says ' data may exist in the sheets you want to delete'
ASKER
Hi, ignore that last post please - I just do this using the line below to turn them off
Application.DisplayAlerts = False
Thanks again
Application.DisplayAlerts = False
Thanks again
You're welcome. :)
Put the code below before the wb.Worksheets("Data").Dele te line.
Put the code below before the wb.Worksheets("Data").Dele
Application.DisplayAlerts = False