My Macro is copying formula's instead of pasting values

[AJanaway]

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 values and removes a few tabs to generate a 'cleaned up' version of my report. 

When I run the macro, everything works as it should apart from the copy values part. Instead of just copying the cell value, the formula's are being copied and they are trying to pull from one of the tabs I have deleted in the cleaned up version.

Please see formula I am using below:

 For Each Ws In ActiveWorkbook.Worksheets

Range("A1").Select
Cells.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Any help would be greatly appreciated 🙂

[#2]

@AJanaway 

Does this work better?

    Dim Ws As Worksheet
    For Each Ws In Worksheets
        With Ws.UsedRange
            .Value = .Value
        End With
    Next Ws

I'd run this before deleting sheets.

[#3]

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 Sub

[#4 · Accepted solution]

@AJanaway 

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

[#5]

My friend you are a lifesaver. Thank you so much!