Select method of worksheet class failed

I have this sub in Excel 2010 which is intended to filter through all the cells in a sheet until it finds a complement to Proj No, then paste a field from this row into another area.

When I attempt to run the sub, it gives me an error 1004: Select Method of Worksheet Class Failed. I"ve noted the line where this occurs. Any assistance would be greatly appreciated.

Option ExplicitPrivate Sub btnNext_Click()Dim ProjNo As StringDim Col As StringDim Row As StringDim cell As RangeUnfill DialogformWait.ShowSheets("Sheet7").ActivateProjNo = Worksheets("Sheet1").Range("D6").ValueCol = Cells(Rows.Count, "A").End(xlUp).RowFor Each cell In Range("A2:A" & Col) If cell.Value = ProjNo Then Row = Row & cell.Row End IfNext cellWorkbooks("Form.xlsm").Sheets("Sheet7").Range("Row, 6").Copy Destination:=Sheets("Sheet1").Range("19, 5") ‘ErrorUnfill formWaitEnd Sub

You watching: Select method of worksheet class failed

vba excel
Improve this question
edited Jul 22 "14 at 14:21
asked Jul 21 "14 at 19:12


61411 gold badge77 silver badges2727 bronze badges
Add a comment |

4 Answers 4

See more: How To Use Chat In Minecraft Xbox One, Game Chat : Minecraft

Active Oldest Votes
I don"t recognize what GWP is, but I think you want to use ProjNo tright here. The Range building does not accept an discussion like that. Unless you have actually a called array of "Row,6" which you don"t because it"s not a legal name, you have to supply Range through a valid range reference, favor A6 or D2:D12, for instance.

Also, you can"t concatenate rows and also usage them in a Range referral to obtain a larger variety. You would have to copy each row inside the loop, union the varieties as you go, or better yet, filter on the worth that you want and also copy the visible rows.

Try this:

Private Sub btnNext_Click() With ThisWorkbook.Worksheets("Sheet7") "filter for the job id .Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 6).AutoFilter 1, "=" & .Range("D6").Value "copy the visible rows .Range("F2", .Cells(.Rows.Count, 6).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy _ ThisWorkbook.Worksheets("Sheet1").Cells(19, 5) "get rid of the filter .AutoFilterSetting = False End WithEnd Below
Improve this answer
answered Jul 21 "14 at 19:48


Penis KusleikaDick Kusleika
31k33 gold badges4848 silver badges6868 bronze badges
Add a comment |

See more: How To Fix: Windows 10 How Do You Want To Open This File Keeps Popping Up

Tbelow are a few confutilizing items in your code over, so I wanted to area them long-develop below. Let"s acquire started:

Dim Col As StringDim Row As StringIt looks like your style expects these to be of form Long quite than kind String. Even if these variables were supposed to be strings, I would certainly recommend adjusting their names -- when your fellow developer attempts to testimonial your architecture, he or she is most likely to see names prefer "Col" or "Row" and also think "these are numbers". Easy fix:

Dim Col As Long, Row As LongThe next worry comes up here:

Col = Cells(Rows.Count, "A").End(xlUp).RowThe structure over is a common approach for identifying the last ROW, not column. (It also shows up that you have actually switched the "A" and number, which is an additional easy fix). While it is perfectly acceptable syntactically to name the variable for last row "Col", humale individuals are likely to discover this confusing. Identifying the last row (and the last col, which you usage in the For Each loop), as explained in excellent information here, would be better taken on favor this:

Dim SheetSalso As Worksheet, SheetOne As WorksheetDim LastRow As Long, LastCol As LongSet SheetSeven = ThisWorkbook.Worksheets("Sheet7")Set SheetOne = ThisWorkbook.Worksheets("Sheet1")With SheetSalso LastRow = .Range("A" & .Rows.Count).End(xlUp).Row LastCol = .Range("A" & .Columns.Count).End(xlToLeft).Shaft End WithThis need to make your For Each loop look prefer this:

With SheetSeven For Each cell in .Range("A2:A" & LastCol) "... perform you comparikid and also row incrementing here Next cellEnd WithOnce you"ve identified your sheet as a variable, the Range.Copy action should be a lot much easier as well:

With SheetSeven .Range(.Cells(Row, 6)).Copy _ Destination:=SheetOne.Range(SheetOne.Cells(19, 5))End With