Macro won't paste in second sheet "Run-time error:9" Out ofRange

[Closed]
Report
Posts
1
Registration date
Monday March 3, 2014
Status
Member
Last seen
March 3, 2014
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
I am trying to copy from sheet 1 and paste in sheet 2.

Checking row "G" for the letter "x", the "x" marks the rows I would like to copy from sheet1 over to sheet2. I seem to get an error. Other's have used similar code on the forum but no one seems to have the out of range error. So I am looking for a little help. When I step through to debug, the error occurs after "Range(Cells... ...Copy" and I can see of my spreadsheet where the proper cells are highlighted.

Private Sub CommandButton1_Click()

Dim ws1 As Worksheet
Set ws1 = Sheets(1)

Dim ws2 As Worksheet
Set ws2 = Sheets(2)

Dim ws3 As Worksheet
Set ws3 = Sheets(3)

For Each cell In ws1.Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row)
If cell.Value = "x" Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Copy
Sheets("ws2").Select
NextRow = Cells(Rows.Count, "A").End(x1Up).Row
Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If
Next cell

End Sub

Thanks in advance! Here's the file link. http://speedy.sh/5Y3b8/Log-update-macro.xls

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
at a quick reading which is the ACTIVE SHEET. AT THE BEGINING

after this code
Set ws3 = Sheets(3)
introduce
worksheets(<sheet name in double quotes>).activate

try this and still any problem get back to newsgrop