This VBA code works OK if I run it inside the Sheet2,
but it fails if I try to start it with a command-button
located in Sheet1.

Sheet2.Cells(2, 4) = Sheet2.Cells(2, 3)
j = j - 1
Sheet2.Cells(j, 5) = Sheet2.Cells(j, 3)
' Sheets("Sheet2").Activate
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
Sheet2.Range(Cells(2, 1), Cells(j, 2)).Name = "filterAB"
End Sub

It fails at this line
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
and I get the message
Run-time error '1004'
Method 'Range' of object '_Worksheet' failed

I tried to add this line (it is now a comment)
Sheets("Sheet2").Activate
and that did no good.
--
jo

On Thursday, 22 March 2012 17:31:37 UTC+5:30, Jo Stein wrote:
This VBA code works OK if I run it inside the Sheet2,
but it fails if I try to start it with a command-button
located in Sheet1.

Sheet2.Cells(2, 4) = Sheet2.Cells(2, 3)
j = j - 1
Sheet2.Cells(j, 5) = Sheet2.Cells(j, 3)
' Sheets("Sheet2").Activate
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
Sheet2.Range(Cells(2, 1), Cells(j, 2)).Name = "filterAB"
End Sub

It fails at this line
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
and I get the message
Run-time error '1004'
Method 'Range' of object '_Worksheet' failed

I tried to add this line (it is now a comment)
Sheets("Sheet2").Activate
and that did no good.
--
jo



On Thursday, 22 March 2012 17:31:37 UTC+5:30, Jo Stein wrote:
This VBA code works OK if I run it inside the Sheet2,
but it fails if I try to start it with a command-button
located in Sheet1.

Sheet2.Cells(2, 4) = Sheet2.Cells(2, 3)
j = j - 1
Sheet2.Cells(j, 5) = Sheet2.Cells(j, 3)
' Sheets("Sheet2").Activate
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
Sheet2.Range(Cells(2, 1), Cells(j, 2)).Name = "filterAB"
End Sub

It fails at this line
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
and I get the message
Run-time error '1004'
Method 'Range' of object '_Worksheet' failed

I tried to add this line (it is now a comment)
Sheets("Sheet2").Activate
and that did no good.
--
jo

Try using
Sheets("Sheet2").Select
before offending line.

Regards,
Madiya

Hi Jo,

Am Thu, 22 Mar 2012 13:01:37 +0100 schrieb Jo Stein:

It fails at this line
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
and I get the message
Run-time error '1004'
Method 'Range' of object '_Worksheet' failed

try this:
With Sheets("Sheet2")
.Cells(2, 4) = .Cells(2, 3)
j = j - 1
.Cells(j, 5) = .Cells(j, 3)
End With
Sheets("Sheet2").Select
ActiveWorkbook.Names.Add Name:="filterA", _
RefersTo:=Range(Cells(2, 1), Cells(j, 1))
ActiveWorkbook.Names.Add Name:="filterB", _
RefersTo:=Range(Cells(2, 1), Cells(j, 2))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Put your code in a standard module. (not the module behind a sheet)
Qualify "Cells" with its parent name.
Use the Add method of the Names collection...

Sheet2.Names.Add Name:="filterA", RefersTo:=Sheet2.Range(Sheet2.Cells(2, 1), Sheet2.Cells(j, 1))
Sheet2.Names.Add Name:="filterAB", RefersTo:=Sheet2.Range(Sheet2.Cells(2, 1), Sheet2.Cells(j, 2))
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)




"Jo Stein"
wrote in message
...
This VBA code works OK if I run it inside the Sheet2,
but it fails if I try to start it with a command-button
located in Sheet1.

Sheet2.Cells(2, 4) = Sheet2.Cells(2, 3)
j = j - 1
Sheet2.Cells(j, 5) = Sheet2.Cells(j, 3)
' Sheets("Sheet2").Activate
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
Sheet2.Range(Cells(2, 1), Cells(j, 2)).Name = "filterAB"
End Sub

It fails at this line
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
and I get the message
Run-time error '1004'
Method 'Range' of object '_Worksheet' failed

I tried to add this line (it is now a comment)
Sheets("Sheet2").Activate
and that did no good.
--
jo

Den 22.03.2012 13:47, skrev Jim Cone:
Put your code in a standard module. (not the module behind a sheet)
Qualify "Cells" with its parent name.
Use the Add method of the Names collection...

Sheet2.Names.Add Name:="filterA", RefersTo:=Sheet2.Range(Sheet2.Cells(2, 1), Sheet2.Cells(j, 1))
Sheet2.Names.Add Name:="filterAB", RefersTo:=Sheet2.Range(Sheet2.Cells(2, 1), Sheet2.Cells(j, 2))

Thanks a lot.
Sheets("Sheet2").Select was no good, and you solved the problem.
--
jo

The problem is that you don't qualify your ranges.

In code like this:
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"

the unqualified Cells() will behave differently depending on where the code is
located.

If the code is in a General module, then the cells() will refer to the activesheet.

If the code is in a worksheet module, then the cells() will refer to the sheet
that owns the code.

You could qualify the ranges like:

Sheet2.Range(sheet2.Cells(2, 1), sheet2.Cells(j, 1)).Name = "filterA"

or use a with statement:

with Sheet2
.Range(.Cells(2, 1), .Cells(j, 1)).Name = "filterA"
end with



On 03/22/2012 07:01, Jo Stein wrote:
This VBA code works OK if I run it inside the Sheet2,
but it fails if I try to start it with a command-button
located in Sheet1.

Sheet2.Cells(2, 4) = Sheet2.Cells(2, 3)
j = j - 1
Sheet2.Cells(j, 5) = Sheet2.Cells(j, 3)
' Sheets("Sheet2").Activate
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
Sheet2.Range(Cells(2, 1), Cells(j, 2)).Name = "filterAB"
End Sub

It fails at this line
Sheet2.Range(Cells(2, 1), Cells(j, 1)).Name = "filterA"
and I get the message
Run-time error '1004'
Method 'Range' of object '_Worksheet' failed

I tried to add this line (it is now a comment)
Sheets("Sheet2").Activate
and that did no good.

--
Dave Peterson