Forum Discussion
My Macro is copying formula's instead of pasting values
Hi All, I have an excel spreadsheet that I generate using data that pulls from a few different tabs within the spreadsheet. I am trying to run a macro that copies everything in each tab as value...
That does not appear to be the full macro - it would fail since you don't assign a value to the variable fil. anyway, the code could look like this:
Sub SaveThisReport() Dim fil As String Dim Ws As Worksheet Dim LMonth As Integer Dim LValue As String 'Get current month number LMonth = Month(Date) LYear = Year(Date) LValue = Format(LMonth, "00") tmpYear = LYear Application.DisplayAlerts = False 'Save report ActiveWorkbook.SaveAs fil For Each Ws In Worksheets With Ws.UsedRange .Value = .Value End With Next Ws Sheets("Control_Sheet").Delete Sheets("Sheet 1").Select Range("A1").Select ActiveWorkbook.SaveAs fil Application.DisplayAlerts = True MsgBox "Process Complete", vbOKOnly, "Procedure Update" End Sub
Does this work better?
Dim Ws As Worksheet
For Each Ws In Worksheets
With Ws.UsedRange
.Value = .Value
End With
Next WsI'd run this before deleting sheets.
- Thanks for the response 🙂
This is my full macro, where abouts would I insert the code you have sent me above?
Sub SaveThisReport()
Dim fil As String
Dim Ws As Worksheet
Dim LMonth As Integer
Dim LValue As String
'Get current month number
LMonth = Format(DateSerial(Year(Now), Month(Now) - 0, 1), "MM")
LYear = Year(Now)
LValue = Format(LMonth, "00")
tmpYear = LYear
Application.DisplayAlerts = False
'Save report
ActiveWorkbook.SaveAs (fil)
Sheets("Sheet 1").Select
For Each Ws In ActiveWorkbook.Worksheets
Range("A1").Select
Range("A1:EA500").Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
On Error Resume Next
Worksheets(ActiveSheet.Index + 1).Select
Next
'Range("B1").Select
Sheets("Control_Sheet").Delete
Sheets("Sheet 1").Select
Range("A1").Select
ActiveWorkbook.SaveAs (fil)
MsgBox "Process Complete", vbOKOnly, "Procedure Update"
End SubThat does not appear to be the full macro - it would fail since you don't assign a value to the variable fil. anyway, the code could look like this:
Sub SaveThisReport() Dim fil As String Dim Ws As Worksheet Dim LMonth As Integer Dim LValue As String 'Get current month number LMonth = Month(Date) LYear = Year(Date) LValue = Format(LMonth, "00") tmpYear = LYear Application.DisplayAlerts = False 'Save report ActiveWorkbook.SaveAs fil For Each Ws In Worksheets With Ws.UsedRange .Value = .Value End With Next Ws Sheets("Control_Sheet").Delete Sheets("Sheet 1").Select Range("A1").Select ActiveWorkbook.SaveAs fil Application.DisplayAlerts = True MsgBox "Process Complete", vbOKOnly, "Procedure Update" End Sub- My friend you are a lifesaver. Thank you so much!
