Forum Discussion

AJanaway's avatar
AJanaway
Copper Contributor
Aug 13, 2024

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 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 🙂

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Aug 15, 2024

    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
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 13, 2024

    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.

    • AJanaway's avatar
      AJanaway
      Copper Contributor
      Aug 15, 2024
      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
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 15, 2024

        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

Resources