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
Sheet.Activate
Set CurrentWindow = ActiveWindow
Application.Goto Reference:="R1C1"
Cells.Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End If
Next Sheet

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

CurrentSheet.Activate

End Sub
_______________________________________


No comments: