Help on a copy/paste issue

[judgejustin]

I am running this code and everything works up to the first line where it's suppose to paste. The next couple of lines after the first paste line may not work either but I haven't gotten that far in testing. I have played around with everything I know and can't figure this out.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 14 Then Exit Sub
Dim FilteToOpen As Variant
Dim OpenBook As Workbook
sPath = ThisWorkbook.Path & "\"
Application.ScreenUpdating = False

Workbooks.Open Filename:=sPath & ActiveCell.Offset(, -11) & ".xlsm", UpdateLinks:=True




Sheets("AGR").Range("K11").Copy
ThisWorkbook("County Data File.xlsm").Worksheets("Statistics").Range(ActiveCell).PasteSpecial xlPasteValues
OpenBook.Sheets("AGR").Range("K9").Copy
ThisWorkbook.Worksheets("Statistics").Range(ActiveCell.Offset(, 1)).PasteSpecial xlPasteValues
OpenBook.Close True


Application.ScreenUpdating = True

End Sub

 

[MARK858]

ActiveCell is already a range so Range(ActiveCell) should just be ActiveCell, but if you are using the activecell why would you need the

ThisWorkbook("County Data File.xlsm").Worksheets("Statistics")

part?
Can you please explain exactly where you are pasting and also let us know what workbook and worksheet are currently active please?

 

[GWteB]

You're probably looking for something like this ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim OpenBook    As Workbook
    Dim spath       As String
    
    If Target.Column <> 14 Then Exit Sub
    spath = ThisWorkbook.Path & "\"
    Application.ScreenUpdating = False
    
    Set OpenBook = Workbooks.Open(Filename:=spath & Target.Offset(, -11) & ".xlsm", UpdateLinks:=True)
    With OpenBook
        .Sheets("AGR").Range("K11").Copy
        ThisWorkbook.Worksheets("Statistics").Target.PasteSpecial xlPasteValues
        .Sheets("AGR").Range("K9").Copy
        ThisWorkbook.Worksheets("Statistics").Target.Offset(, 1).PasteSpecial xlPasteValues
        .Close True
    End With
    
    Application.ScreenUpdating = True

End Sub

 

[judgejustin]

It still will not paste starting at the first line of code.

ThisWorkbook.Worksheets("Statistics").Target.PasteSpecial xlPasteValues

It is doing everything up to that point.
I get a runtime error 438 "Object doesn't support this property or method"

 

[GWteB]

Was a bit too enthusiastic with the use of Find & Replace
Indeed, these two lines of code will never work.

       ThisWorkbook.Worksheets("Statistics").Target.PasteSpecial xlPasteValues
       ThisWorkbook.Worksheets("Statistics").Target.Offset(, 1).PasteSpecial xlPasteValues

In both lines Target should be replaced by the destination Cell (eg like Range("K10")) in which you're pasting.
ThisWorkbook refers to the workbook in which this code is running, so worksheet "Statistics" must exist.
If it doesn't work for you, explain us exactly what you're trying to do.