Select Method of Worksheet Class Failed..Activate ProjNo = Worksheets('Sheet1').Range('D6'). The structure above is a common method for identifying the last. ![]() ![]() ![]() I have this sub in Excel 2010 which is supposed to filter through all the cells in a sheet until it finds a match to Proj No, then paste a field from this row into another field. When I try to run the sub, it gives me an error 1004: Select Method of Worksheet Class Failed. I've marked the line where this occurs. Any assistance would be greatly appreciated. Option Explicit Private Sub btnNext_Click() Dim ProjNo As String Dim Col As String Dim Row As String Dim cell As Range Unload Dialog formWait.Show Sheets('Sheet7').Activate ProjNo = Worksheets('Sheet1').Range('D6').Value Col = Cells(Rows.Count, 'A').End(xlUp).Row For Each cell In Range('A2:A' & Col) If cell.Value = ProjNo Then Row = Row & cell.Row End If Next cell Workbooks('Form.xlsm').Sheets('Sheet7').Range('Row, 6').Copy Destination:=Sheets('Sheet1').Range('19, 5') ‘Error Unload formWait End Sub. I don't know what GWP is, but I think you want to use ProjNo there. The Range property doesn't accept an argument like that. Unless you have a named range of 'Row,6' which you don't because it's not a legal name, you have to supply Range with a valid range reference, like A6 or D2:D12, for example. Also, you can't concatenate rows and use them in a Range reference to get a larger range. You would have to copy each row inside the loop, union the ranges as you go, or better yet, filter on the value that you want and copy the visible rows. Try this: Private Sub btnNext_Click() With ThisWorkbook.Worksheets('Sheet7') 'filter for the project 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.AutoFilterMode = False End With End Sub. There are a few confusing items in your code above, so I wanted to place them long-form here. Let's get started: Dim Col As String Dim Row As String It looks like your design expects these to be of type Long rather than type String. Even if these variables were meant to be strings, I would recommend adjusting their names -- when your fellow developer attempts to review your design, he or she is likely to see names like 'Col' or 'Row' and think 'these are numbers'. Easy fix: Dim Col As Long, Row As Long The next issue comes up here: Col = Cells(Rows.Count, 'A').End(xlUp).Row The structure above is a common method for identifying the last ROW, not column. (It also appears that you have switched the 'A' and number, which is another easy fix). While it is perfectly acceptable syntactically to name the variable for last row 'Col', human users are likely to find this confusing. Identifying the last row (and the last col, which you use in the For Each loop), as explained in fantastic detail, would be better handled like this: Dim SheetSeven As Worksheet, SheetOne As Worksheet Dim LastRow As Long, LastCol As Long Set SheetSeven = ThisWorkbook.Worksheets('Sheet7') Set SheetOne = ThisWorkbook.Worksheets('Sheet1') With SheetSeven LastRow =.Range('A' &.Rows.Count).End(xlUp).Row LastCol =.Range('A' &.Columns.Count).End(xlToLeft).Column End With This should make your For Each loop look like this: With SheetSeven For Each cell in.Range('A2:A' & LastCol) '. Do you comparison and row incrementing here Next cell End With Once you've identified your sheet as a variable, the Range.Copy action should be much easier as well: With SheetSeven.Range(.Cells(Row, 6)).Copy _ Destination:=SheetOne.Range(SheetOne.Cells(19, 5)) End With.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2018
Categories |