Thursday, May 21, 2009

Created an Excel macro that 'freezes' information on all worksheets of a workbook

Our 25-worksheet monthly report workbook has gobs of calculations throughout. Information is even pulled from another workbook to complete the calculations. Problem has occurred when sending the final monthly report workbook to the customer - I would have to manually open each worksheet, highlight it, copy it, and use Edit / Paste / Special to paste only the Values. Too time-consuming, so I worked up the following macro:

Sub EditPasteValues()

' I'll want to return to the current worksheet
' when this is done, so I'll save its info for
' future reference:

Dim CurrentSheet As Excel.Worksheet
Set CurrentSheet = ActiveSheet

Dim Sheet As Excel.Worksheet
Dim CurrentWindow As Window

' Here, I'll cycle through each of the worksheets,
' performing the select all cells, copy all cells,
' Edit / Paste Special / Values so that any formulas
' are wiped out and only the final values remain

For Each Sheet In Excel.Worksheets
If TypeName(Sheet) = "Worksheet" Then
Set CurrentWindow = ActiveWindow
Application.Goto Reference:="R1C1"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
Next Sheet

' Here I'm returning to the original worksheet
' that I saved above


End Sub

No comments: