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

Closed
Huffman09 Posts 1 Registration date Monday March 3, 2014 Status Member Last seen March 3, 2014 - Mar 3, 2014 at 04:56 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Mar 3, 2014 at 10:40 PM
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
Related:

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 3, 2014 at 10:40 PM
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
0