Sorting Macro question
 

1. I have a hiden worksheet that I would like to sort. So far, I am only
able to get the macro to work if I make the worksheet active in order to
sort. I need to keep the sheet hidden. Is there a way to sort a hidden
worksheet?

2. In my spreadsheet I have certain rows that are colored in background RED.
I would like to use the IF in a cell to say something like "IF A1=background
in RED, A1+2, A1*2". Is it possible?

Thanks
Kim

Sorting Macro question
 

Re #1, You CAN sort a worksheet that isn't active. Please post the code you
have.

Re #2, see www.cpearson.com for help with identifying background colors. BTW,
if the color is a result of conditional formatting, you can't determine what
it is by VBA code. You would have to see which of the conditional formatting
conditions is met.

On Sat, 23 Oct 2004 17:58:09 -0700, "kim" wrote:

1. I have a hiden worksheet that I would like to sort. So far, I am only
able to get the macro to work if I make the worksheet active in order to
sort. I need to keep the sheet hidden. Is there a way to sort a hidden
worksheet?

2. In my spreadsheet I have certain rows that are colored in background RED.
I would like to use the IF in a cell to say something like "IF A1=background
in RED, A1+2, A1*2". Is it possible?

Thanks
Kim

Sorting Macro question
 

The code I use for sorting.
Worksheets("Sheet1").Range("A1:B7").sort Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

It's giving me an error stating that "Sort reference is not valid."

"Myrna Larson" wrote in message
...
Re #1, You CAN sort a worksheet that isn't active. Please post the code
you
have.

Re #2, see www.cpearson.com for help with identifying background colors.
BTW,
if the color is a result of conditional formatting, you can't determine
what
it is by VBA code. You would have to see which of the conditional
formatting
conditions is met.

On Sat, 23 Oct 2004 17:58:09 -0700, "kim" wrote:

1. I have a hiden worksheet that I would like to sort. So far, I am only
able to get the macro to work if I make the worksheet active in order to
sort. I need to keep the sheet hidden. Is there a way to sort a hidden
worksheet?

2. In my spreadsheet I have certain rows that are colored in background
RED.
I would like to use the IF in a cell to say something like "IF
A1=background
in RED, A1+2, A1*2". Is it possible?

Thanks
Kim

Sorting Macro question
 

With Worksheets("Sheet1")
.Range("A1:B7").sort Key1:=.Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End with

the Range("B1") refers to the active sheet since it isn't qualified, so the
above qualifies it.

--
Regards,
Tom Ogilvy


"kim" wrote in message
...
The code I use for sorting.
Worksheets("Sheet1").Range("A1:B7").sort Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

It's giving me an error stating that "Sort reference is not valid."

"Myrna Larson" wrote in message
...
Re #1, You CAN sort a worksheet that isn't active. Please post the code
you
have.

Re #2, see www.cpearson.com for help with identifying background colors.
BTW,
if the color is a result of conditional formatting, you can't determine
what
it is by VBA code. You would have to see which of the conditional
formatting
conditions is met.

On Sat, 23 Oct 2004 17:58:09 -0700, "kim" wrote:

1. I have a hiden worksheet that I would like to sort. So far, I am only
able to get the macro to work if I make the worksheet active in order to
sort. I need to keep the sheet hidden. Is there a way to sort a hidden
worksheet?

2. In my spreadsheet I have certain rows that are colored in background
RED.
I would like to use the IF in a cell to say something like "IF
A1=background
in RED, A1+2, A1*2". Is it possible?

Thanks
Kim

Sorting Macro question
 

The error is because of the way you are specifying the key. You write simpley
Range("B1"). That, but definition, is a range on the active sheet. You can't
sort Sheet1 according to a column on a different sheet. You must qualify the
range, i.e.

Worksheets("Sheet1").Range("A1:B7").sort _
Key1:=Worksheet("Sheet1").Range("B1"), _

Or, to shorten the code

With Worksheets("Sheet1")
.Range("A1:B7").sort Key1:=.Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With

Note the periods before each of the Range key words.

On Sat, 23 Oct 2004 18:46:04 -0700, "kim" wrote:

The code I use for sorting.
Worksheets("Sheet1").Range("A1:B7").sort Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

It's giving me an error stating that "Sort reference is not valid."

"Myrna Larson" wrote in message
.. .
Re #1, You CAN sort a worksheet that isn't active. Please post the code
you
have.

Re #2, see www.cpearson.com for help with identifying background colors.
BTW,
if the color is a result of conditional formatting, you can't determine
what
it is by VBA code. You would have to see which of the conditional
formatting
conditions is met.

On Sat, 23 Oct 2004 17:58:09 -0700, "kim" wrote:

1. I have a hiden worksheet that I would like to sort. So far, I am only
able to get the macro to work if I make the worksheet active in order to
sort. I need to keep the sheet hidden. Is there a way to sort a hidden
worksheet?

2. In my spreadsheet I have certain rows that are colored in background
RED.
I would like to use the IF in a cell to say something like "IF
A1=background
in RED, A1+2, A1*2". Is it possible?

Thanks
Kim

Sorting Macro question
 

Boy, I think I have a record for typos the

simpley should be simply
but should be by
Key1:= Worksheet( should be Key1:=Worksheets(

On Sat, 23 Oct 2004 21:11:42 -0500, Myrna Larson
wrote:

The error is because of the way you are specifying the key. You write simpley
Range("B1"). That, but definition, is a range on the active sheet. You can't
sort Sheet1 according to a column on a different sheet. You must qualify the
range, i.e.

Worksheets("Sheet1").Range("A1:B7").sort _
Key1:=Worksheet("Sheet1").Range("B1"), _

Or, to shorten the code

With Worksheets("Sheet1")
.Range("A1:B7").sort Key1:=.Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With

Note the periods before each of the Range key words.

On Sat, 23 Oct 2004 18:46:04 -0700, "kim" wrote:

The code I use for sorting.
Worksheets("Sheet1").Range("A1:B7").sort Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

It's giving me an error stating that "Sort reference is not valid."

"Myrna Larson" wrote in message
. ..
Re #1, You CAN sort a worksheet that isn't active. Please post the code
you
have.

Re #2, see www.cpearson.com for help with identifying background colors.
BTW,
if the color is a result of conditional formatting, you can't determine
what
it is by VBA code. You would have to see which of the conditional
formatting
conditions is met.

On Sat, 23 Oct 2004 17:58:09 -0700, "kim" wrote:

1. I have a hiden worksheet that I would like to sort. So far, I am only
able to get the macro to work if I make the worksheet active in order to
sort. I need to keep the sheet hidden. Is there a way to sort a hidden
worksheet?

2. In my spreadsheet I have certain rows that are colored in background
RED.
I would like to use the IF in a cell to say something like "IF
A1=background
in RED, A1+2, A1*2". Is it possible?

Thanks
Kim

Sorting Macro question
 

Thanks everyone for your quick response. It's working now.

"Myrna Larson" wrote in message
...
The error is because of the way you are specifying the key. You write
simpley
Range("B1"). That, but definition, is a range on the active sheet. You
can't
sort Sheet1 according to a column on a different sheet. You must qualify
the
range, i.e.

Worksheets("Sheet1").Range("A1:B7").sort _
Key1:=Worksheet("Sheet1").Range("B1"), _

Or, to shorten the code

With Worksheets("Sheet1")
.Range("A1:B7").sort Key1:=.Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With

Note the periods before each of the Range key words.

On Sat, 23 Oct 2004 18:46:04 -0700, "kim" wrote:

The code I use for sorting.
Worksheets("Sheet1").Range("A1:B7").sort Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

It's giving me an error stating that "Sort reference is not valid."

"Myrna Larson" wrote in message
. ..
Re #1, You CAN sort a worksheet that isn't active. Please post the code
you
have.

Re #2, see www.cpearson.com for help with identifying background colors.
BTW,
if the color is a result of conditional formatting, you can't determine
what
it is by VBA code. You would have to see which of the conditional
formatting
conditions is met.

On Sat, 23 Oct 2004 17:58:09 -0700, "kim" wrote:

1. I have a hiden worksheet that I would like to sort. So far, I am only
able to get the macro to work if I make the worksheet active in order to
sort. I need to keep the sheet hidden. Is there a way to sort a hidden
worksheet?

2. In my spreadsheet I have certain rows that are colored in background
RED.
I would like to use the IF in a cell to say something like "IF
A1=background
in RED, A1+2, A1*2". Is it possible?

Thanks
Kim