Copy Paste Values - Entire Workbook and Save

[Guest]

Please help. I have a workbook with multiple tabs. Each tab has hundreds of
formulas that refer to other spreadsheets and/or databases.

Each month, I need to be able to

A) Copy / PasteSpecial Values for all contents of each tab, so that no
formulas exist, just static values

and

B) Save the workbook as a new workbook with the name located in Cell A1,
Sheet1

My guess is that it would be three macros.
Macro 1 would do the Paste/Values for each tab
Macro 2 would do the Save As
Macro 3 would run Macro1 then Macro2

Thanks in advance for any help. It is much appreciated.

Scott

 

[Tim879]

try this code:

ub Copy_Paste_Values_On_All_tabs()
'
' Copy_Paste_Values_On_All_tabs Macro
' Macro recorded 2/15/2007 by TB
' This macro cycles through each tab, selecting all data on the tab,
copying and then pasting
' the values
'

Dim myCount 'This line of code is optional
Dim i 'This line of code is optional


'Protect All Formulas First
response = MsgBox("Do you want to copy / paste values on ALL TABS?",
vbOKCancel)

If response = vbOK Then MsgBox ("Formulas Will Be Replaced With
Values") Else MsgBox ("Cancelled")

If response = vbOK Then
myCount = Application.Sheets.Count

NumSheets = ActiveWorkbook.Sheets.Count
For i = 1 To NumSheets
Sheets(i).Select

Cells.Select ' select all cells
Selection.Copy ' Copy selection
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False ' paste values
Application.CutCopyMode = False ' exit copy / paste mode
Range("A1").Select

Next

End If
End Sub