Solved: Method 'Range' of object '_Worksheet' failed when that worksheet is not active.

[nb-]

I keep getting the following error;

Method 'Range' of object '_Worksheet' failed when trying to use the



myrange.worksheet


property, and nothing I can find seems to quite address my problem. The ranges is defined as;



set myrange = ActiveWorkbook.Sheets("Sheet1").range("A11:BF11")


This error only occurs when I run the code and sheet1 is not the active sheet.

The actual code is copying down some excel formulas from one row (11) down 'lng_t_steps' rows, and deleting any past that.

Any ideas on a solution that (prefrably dont involve activating the sheet, as it will entail a lot of sheet switching in the more general version)?

Thanks.



Public Sub test()

Dim dbl_t_step As Double
Dim lng_t_steps As Long
Dim rng_start_formula As range

Dim str_range_start As String
Dim str_range_end As String
Dim temp_str As String

lng_t_steps = 10
Set rng_start_formula = ActiveWorkbook.Sheets("Sheet1").range("A11:BF11")

str_range_start = Split_text(rng_start_formula.Address, 1, ":")
str_range_end = Split_text(rng_start_formula.Offset(lng_t_steps, 0).Address, 2, ":")

'// Copy down formula
rng_start_formula.AutoFill Destination:=rng_start_formula.Worksheet.range(range(str_range_start), range(str_range_end)), Type:=xlFillDefault

'// clear extra formulas
rng_start_formula.Worksheet.range(range(range(str_range_start).Offset(lng_t _steps + 1, 0).Address), _
range(rng_start_formula.Worksheet.range(str_range_end).End(xlDown).Address) ).Clear

end sub

[mdmackillop]

Try this method, You need to qualify each part of a range when working from another sheet.
I've altered your code a bit, as I don't have SplitText function


Public Sub test()

Dim dbl_t_step As Double
Dim lng_t_steps As Long
Dim rng_start_formula As Range

Dim str_range_start As String
Dim str_range_end As String
Dim temp_str As String

Dim WS As Worksheet
Set WS = ActiveWorkbook.Sheets("Sheet1")
lng_t_steps = 10
Set rng_start_formula = WS.Range("A11:BF11")

str_range_start = Split(rng_start_formula.Address, ":")(0)
str_range_end = Split(rng_start_formula.Offset(lng_t_steps, 0).Address, ":")(1)

'// Copy down formula
rng_start_formula.AutoFill Destination:=WS.Range(rng_start_formula, WS.Range(str_range_end)), Type:=xlFillDefault

'// clear extra formulas
'rng_start_formula.Worksheet.Range(Range(Range(str_range_start).Offset(lng_ t_steps + 1, 0).Address), _
'Range(rng_start_formula.Worksheet.Range(str_range_end).End(xlDown).Address )).Clear

End Sub

[nb-]

Thanks, mdmackillop.

My mistake leaving the split text function in, its just a wrapper for the vba split function to use it in excel.

What I guess I was really after was a way of getting the worksheet a range refers to out of the range, for example if the sub is passed the range rather than it being set in the sub.

It looks as though you have to pass the worksheet separately though?

[Bob Phillips]

No, you can get the worksheet from the range



rng.Parent

[nb-]

Thanks xld


.Parent.Name

Was what i was after :)