0

I have two worksheets in excel and I am trying to copy a value from one and paste it into a specific cell in the other. I have managed to select the other worksheet and copy the value, and even select back into the other one but for whatever reason I cannot paste the value back in, let alone even select that box! When I add in that part off the code it gives me the error "Run-time error: '438': Object doesn't support this property or method.

I even recorded macro to try and see what it would give me to select the cell and it gave me the exact same thing which was "Range("G4").Select

Here is my code: Sub State_Selector()

Dim x As Integer

For x = 2 To 54
    If Cells(x, 1) = Sheets("Sheet1").Cells(4, 3) Then
        Worksheets("Data").Cells(x, 8).Copy
        Worksheets("Sheet1").Select
        Range("G4").Paste
        
    End If
    
Next x

End Sub

I have tried to run the code now many times, please help!

BigBen
  • 46,229
  • 7
  • 24
  • 40
davidbc
  • 1
  • `Worksheets("Data").Cells(x, 8).Copy Destination:=Worksheets("Sheet1").Range("G4")`. There is no `Range.Paste` method. – BigBen Mar 06 '23 at 18:30
  • Thanks! Is that exactly how it is written? When I type in Destination:=Worksheets it gives me a syntax error. Also, will that paste it or just select the cell? – davidbc Mar 06 '23 at 19:00
  • It's all on one line, or if you prefer two lines, see the first code snippet in the [docs](https://learn.microsoft.com/en-us/office/vba/api/excel.range.copy), which uses the line continuation. – BigBen Mar 06 '23 at 19:03
  • So wierd, I am still getting a "runtime error 9, Subscript out of range". Any clue why? Also, I think it might be something wrong with my settings. I just recorded a macro of selecting and then pasting that box and it gave me a run time error 1004 when I tried to run the recorded macro which makes no sense! – davidbc Mar 06 '23 at 19:17
  • That means the activeworkbook either has no sheet named `Data` or no sheet named `Sheet1`, or both. – BigBen Mar 06 '23 at 19:18
  • I just changed the name of the sheet to something else and it works! Thank you so much for the help!!! – davidbc Mar 06 '23 at 19:26

0 Answers0