Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Paste Values - Entire Workbook and Save
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Paste Values - Entire Workbook and Save
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 On Aug 9, 2:32 pm, Scott Campbell wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I copy and paste an entire workbook? | Excel Worksheet Functions | |||
how do i copy an entire workbook and paste it in another | New Users to Excel | |||
copy all and paste values for all sheets in a workbook | Excel Worksheet Functions | |||
want to copy entire text box and paste in new cell | Excel Discussion (Misc queries) | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions |