Copy Paste Special Values

Can someone suggest an alternative to what I'm doing here. I get an error on the "copy/paste values" here...

Masterbook.ActiveSheet.Range("A:D") = Tempbook.ActiveSheet.Range("A:D").Values

Full Code

Sub UpdateFS()
Application.ScreenUpdating = False
Dim Masterbook As Workbook
Dim Tempbook As Workbook
Dim Filename As String
Dim Pathname As String
Dim i As Integer
Application.DisplayAlerts = False
Set Masterbook = ActiveWorkbook
    For i = 1 To 2
        Filename = Range("M" & i).Text
        Pathname = "C:\Nathan\---COMPLETED REPORTS---\For Kristi\MONTHLY REPORTING\Wellington - Financial Statements\Consolidation\"
        Workbooks.Open Filename:=Pathname & Filename
        Set Tempbook = ActiveWorkbook
        If i = 1 Then
            Masterbook.ActiveSheet.Range("A:D") = Tempbook.ActiveSheet.Range("A:D").Values
            Tempbook.Close
        Else
            Masterbook.ActiveSheet.Range("F:I") = Tempbook.ActiveSheet.Range("A:D").Values
            Tempbook.Close
        End If
    Next i
    Application.DisplayAlerts = True
End Sub

Maybe

Masterbook.ActiveSheet.Range("A:D").Values= Tempbook.ActiveSheet.Range("A:D").Values

VBA Noob

"Object doesn't support this property or method" on this line:

Masterbook.ActiveSheet.Range("A:D").Values = Tempbook.ActiveSheet.Range("A:D").Values

Instead of using Activesheet for both workbook re using the sheet names E.g

Masterbook.Sheets("Sheet1").Range("A:D").Values = Tempbook.Sheets("Sheet1").Range("A:D").Values

VBA Noob

Unfortunately, I cannot specify a sheet name because I need the macro to operate on whatever sheet was selected when the macro started. The sheet name can be different each time I run the macro.

I'm able to get the code to work as follows:

Sub UpdateFS()
Application.ScreenUpdating = False
Dim Masterbook As Workbook
Dim Mybook As String
Dim Pathname As String
Dim i As Integer
Application.DisplayAlerts = False
Set Masterbook = ActiveWorkbook
    For i = 1 To 2
        Mybook = Range("M" & i).Text
        Pathname = "C:\Nathan\---COMPLETED REPORTS---\For Kristi\MONTHLY REPORTING\Wellington - Financial Statements\Consolidation\"
        Workbooks.Open Filename:=Pathname & Mybook
        If i = 1 Then
            ActiveWorkbook.ActiveSheet.Range("A:D").Copy Destination:=Masterbook.ActiveSheet.Range("A:D")
            ActiveWorkbook.Close
        Else
            ActiveWorkbook.ActiveSheet.Range("A:D").Copy Destination:=Masterbook.ActiveSheet.Range("F:I")
            ActiveWorkbook.Close
    
        End If
    Next i
    Application.DisplayAlerts = True
End Sub

The above code was from another expert here, and it works great; however, I would prefer to only snag the values and not also the formats.

Hi,

Modified code so that it paste values only.

Sub UpdateFS()
Application.ScreenUpdating = False
Dim Masterbook As Workbook
Dim Mybook As String
Dim Pathname As String
Dim i As Integer
Application.DisplayAlerts = False
Set Masterbook = ActiveWorkbook
    For i = 1 To 2
        Mybook = Range("M" & i).Text
        Pathname = "C:\Nathan\---COMPLETED REPORTS---\For Kristi\MONTHLY REPORTING\Wellington - Financial Statements\Consolidation\"
        Workbooks.Open Filename:=Pathname & Mybook

        If i = 1 Then
            ActiveWorkbook.ActiveSheet.Range("A:D").Copy
            Masterbook.ActiveSheet.Range("A:D").PasteSpecial (xlValues)
            ActiveWorkbook.Close
        Else
            ActiveWorkbook.ActiveSheet.Range("A:D").Copy
            Masterbook.ActiveSheet.Range("F:I").PasteSpecial (xlValues)
            ActiveWorkbook.Close
    
        End If
    Next i
    Range("A1").Select
    Application.DisplayAlerts = True
End Sub