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 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.

AndrewMcLaughlin

ASKER

mmmmm......I thought so

what about steps 1-3

Markus Fischer

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:

    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

Open in new window

Cheers!
(°v°)

Here is a workbook that will do it.
Remove-Formulas.xlsm

AndrewMcLaughlin

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

Markus Fischer

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°)

Elton Pascua

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

Open in new window

AndrewMcLaughlin

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

AndrewMcLaughlin

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

ASKER CERTIFIED SOLUTION

Elton Pascua

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Elton Pascua

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AndrewMcLaughlin

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'

AndrewMcLaughlin

ASKER

Hi, ignore that last post please - I just do this using the line below to turn them off

Application.DisplayAlerts = False

Thanks again

Elton Pascua

You're welcome. :)

Put the code below before the wb.Worksheets("Data").Delete line.

Application.DisplayAlerts = False

Open in new window