Difficulty converting formulas inserted by VBA to Values

[jwb1012]

Hello, I am using the code below to populate my worksheet with required information. Unfortunately, I have a few fields that I've populated with formulas and I need these to be values. Is there an easy way to do this with my code below? I've tried adding ".Values" in a few places but it doesn't seem to like that (maybe I'm not using this technique properly though?)

Any thoughts and guidance would be appreciated!

Sub Module1()
    Dim wb As ThisWorkbook
    Dim Sh As Worksheet
    Dim CopyRng As Range
    
    Dim Pricing As Worksheet
    Dim BaseDate As Range
    Dim BaseDate_Full As Range
    Dim Heading_Month_1 As Range
    Dim Num_Months As Integer
    
    Dim Dest_Sh As Worksheet
    Dim Dest_Start_Row As Integer
    Dim Dest_End_Row As Integer
    Dim Dest_Start_Row_2 As Integer
    Dim Dest_End_Row_2 As Integer
    
    Dim Source_Sh As Worksheet
    Dim Source_Start_Row As Integer
    Dim Source_End_Row As Integer
        '(1.) Clear contents from destination worksheet
        Set Dest_Sh = Sheets("Destination")
        
            On Error Resume Next
                Dest_Sh.Visible = True
                Dest_Sh.Activate
            On Error GoTo 0
            
            Dest_End_Row = Dest_Sh.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
            Dest_End_Column = Dest_Sh.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
            
                If Dest_End_Row > 1 Then
                    Dest_Sh.Rows("2:" & Dest_End_Row).EntireRow.Delete
                End If
                
                If Dest_End_Column > 2 Then
                    Dest_Sh.Range(Cells(1, 3), Cells(1, Dest_End_Column)).EntireColumn.Delete
                End If
                
        '(2.) Copy source data and paste to destination worksheet
        Set Source_Sh = Sheets("Source")
            Source_End_Row = Source_Sh.Range("D" & Rows.Count).End(xlUp).Row
        
            On Error Resume Next
                Source_Sh.Visible = True
                Source_Sh.Activate
            On Error GoTo 0
            
                'FIELD 1
                Dest_Start_Row = Dest_Sh.Range("A" & Rows.Count).End(xlUp).Row + 1
                Set CopyRng = Source_Sh.Range("B10", "B" & Source_End_Row)
                        CopyRng.Copy
                        With Dest_Sh.Range("A" & Dest_Start_Row)
                            .PasteSpecial 8    ' Column width
                            .PasteSpecial xlPasteValues
                            Application.CutCopyMode = False
                        End With
                        
                'FIELD 2
                Dest_End_Row_2 = Dest_Sh.Cells(Rows.Count, "A").End(xlUp).Row
                Set Pricing = Sheets("Pricing")
                    Pricing.Visible = True
                        Set BaseDate = Pricing.Range("$I$16")
                                BaseDate.Copy
                                With Dest_Sh.Range("B" & Dest_Start_Row, "B" & Dest_End_Row_2)
                                    .PasteSpecial xlPasteValues
                                    Application.CutCopyMode = False
                                End With
                                
                        Set BaseDate_Full = Pricing.Range("$I$14")
                                BaseDate_Full.Copy
                                With Dest_Sh.Range("$C$1")
                                    .PasteSpecial xlPasteValues
                                    Application.CutCopyMode = False
                                End With
                                
                'COLUMN HEADINGS (MONTHS)
                On Error Resume Next
                    Dest_Sh.Visible = True
                    Dest_Sh.Activate
                On Error GoTo 0
                
                Set Heading_Month_1 = Dest_Sh.Range("$C$1")
                Num_Months = Pricing.Range("$I$19")
                    If Heading_Month_1 > 0 Then
[B][I][U]                        Dest_Sh.Range(Cells(1, 4), Cells(1, 2 + Num_Months)).Formula = "=DATE(YEAR(C$1),MONTH(C$1)+1,DAY(C$1))"[/U][/I][/B]
                    End If
                'MONTHLY VALUES
                On Error Resume Next
                    Dest_Sh.Visible = True
                    Dest_Sh.Activate
                On Error GoTo 0
                
                    If Heading_Month_1 > 0 Then
[U][I][B]                        Dest_Sh.Range(Cells(2, 3), Cells(Dest_End_Row_2, 2 + Num_Months)).Formula = "= IFERROR(INDEX(Sub_Resource_Lookup_ID,MATCH($A2,Sub_Resource_Yaxis,0),MATCH(VLOOKUP(C$1,Setup_POP_RangeLookup,3,TRUE),Sub_Resource_Xaxis,0)),0)"[/B][/I][/U]
                    End If
ExitTheSub:
    Application.GoTo Dest_Sh.Cells(1)
    ActiveWindow.DisplayGridlines = False
End Sub

 

[FormR]

Hi, you could try replacing those bold and underlined sections with:

With Dest_Sh.Range(Cells(1, 4), Cells(1, 2 + Num_Months))
    .Formula = Dest_Sh.Range(Cells(1, 4), Cells(1, 2 + Num_Months)).Formula = "=DATE(YEAR(C$1),MONTH(C$1)+1,DAY(C$1))"
    .Value = .Value
End With

And:

With Dest_Sh.Range(Cells(2, 3), Cells(Dest_End_Row_2, 2 + Num_Months))
    .Formula = "= IFERROR(INDEX(Sub_Resource_Lookup_ID,MATCH($A2,Sub_Resource_Yaxis,0),MATCH(VLOOKUP(C$1,Setup_POP_RangeLookup,3,TRUE),Sub_Resource_Xaxis,0)),0)"
    .Value = .Value
End With

 

[jwb1012]

Hmmm this didn't work. It broke and says: "Run-time error 13: Type mismatch after it pasted the first formula in cell C1.

I think the issue is that the formula is for the column headers (months)... so C1 is Jan-1-2016, then I need the macro to drag the formula all the way right so each cell references off the previous cell

C1 = Jan-1-2016
D1 = Feb-1-2016
E1 = Mar-1-2016
F1 = Apr-1-2016

 

[FormR]

Sorry, the first code should be:

With Dest_Sh.Range(Cells(1, 4), Cells(1, 2 + Num_Months))
    .Formula = "=DATE(YEAR(C$1),MONTH(C$1)+1,DAY(C$1))"
    .Value = .Value
End With

 

[jwb1012]

Awesome, thanks a lot! It works perfectly.