PDA

View Full Version : Method 'Range' of object '_Worksheet' failed



coguy1234
01-03-2013, 03:03 PM
I can't seem to get past my error:

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

Any help would be greatly appreciated!

Here is my code:

Sub Sort()

Dim lstRow1 As Long
Dim lstRow2 As Long
Dim lstCol As Integer
Dim SortByRow As Integer
Dim ws1 As Worksheet

'What is the desired sorting method?
If Worksheets("District Summary").Range("$W$6").Value = "District" Then
SortByRow = 2
ElseIf Worksheets("District Summary").Range("$W$6").Value = "Forecast $" Then
SortByRow = 3
ElseIf Worksheets("District Summary").Range("$W$6").Value = "Weighted Forecast $" Then
SortByRow = 4
Else
End If

Set ws1 = Sheets("combined data")

'check what is the last column with data - Force based on common format
lstCol = 19

'check what is the last row with data based on project names
lstRow1 = ws1.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

'Define the range to copy
ws1.Range("A" & 3, Cells(lstRow1, lstCol)).Sort Key1:=ws1.Cells(3, SortByRow), Order1:=xlDescending

End Sub

Kenneth Hobs
01-03-2013, 03:12 PM
Welcome to the forum!

Use F8 in the VBE to step through your code to see which line is the problem. I suspect that your activework does not have one of those sheet names.

You might need to prefix ws1. to your first Cells in the last line.

coguy1234
01-03-2013, 03:18 PM
Hi Kenneth,

I should have stated that it stops on the following line of code:

ws1.Range("A" & 3, Cells(lstRow1, lstCol)).Sort Key1:=ws1.Cells(3, SortByRow), Order1:=xlDescending

And thank you, you were correct. All I had to do was add ws1. before cells.

ws1.Range("A" & 3, ws1.Cells(lstRow1, lstCol)).Sort Key1:=ws1.Cells(3, SortByRow), Order1:=xlDescending

Thanks again,
Greg