I was hoping someone may be able to help me with a problem I am having
writing a macro. I hold my macros in a single excel file that I can
open to allow access to use of the macros. One macro in particular is
causing me some trouble because of this although the fix is probably
pretty simple. I receive an email everyday with an excel spreadsheet
that is out of whack. I am writing a macro that will allow me to
detele colums, arrange rows etc. The worksheet I receive by email
already has a name for the worksheet. I want to change the name of the
current active sheet "which is already named something different
everyday" to "buys" and add new sheets and change their names as well.
When I run the macro I get a runtime error 9 which I have concluded is
the fact that the macro is looking to change the name of worksheet
"XYZ" to "buy" when worksheet "XYZ" does not exist each time I run the
macro. Please see the Macro Below noting the stars next to the issues
at hand.

Columns("A:A").ColumnWidth = 16.43
Columns("E:E").ColumnWidth = 13.86
Columns("A:H").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending,
Key2:=Range("E1") _
, Order2:=xlAscending, Key3:=Range("A1"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
** Sheets("PEPTRANSFILE-010807-090000").Select
** Sheets("PEPTRANSFILE-010807-090000").Name = "Buys"
Sheets("Buys").Select
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Sells"
Sheets("Sells").Select
Sheets.Add
Sheets("Sheet2").Select

How can I make the macro find the "active Sheet" I am on as opposed to
looking for some random named "PEPTRANSFILE-010807-090000" to re-name?

** Sheets("PEPTRANSFILE-010807-090000").Select
** Sheets("PEPTRANSFILE-010807-090000").Name = "Buys"

Assuming that the sheet above is the active sheet:

ActiveSheet.Name = "Buys"

Should work.

"NewUser22" wrote:

I was hoping someone may be able to help me with a problem I am having
writing a macro. I hold my macros in a single excel file that I can
open to allow access to use of the macros. One macro in particular is
causing me some trouble because of this although the fix is probably
pretty simple. I receive an email everyday with an excel spreadsheet
that is out of whack. I am writing a macro that will allow me to
detele colums, arrange rows etc. The worksheet I receive by email
already has a name for the worksheet. I want to change the name of the
current active sheet "which is already named something different
everyday" to "buys" and add new sheets and change their names as well.
When I run the macro I get a runtime error 9 which I have concluded is
the fact that the macro is looking to change the name of worksheet
"XYZ" to "buy" when worksheet "XYZ" does not exist each time I run the
macro. Please see the Macro Below noting the stars next to the issues
at hand.

Columns("A:A").ColumnWidth = 16.43
Columns("E:E").ColumnWidth = 13.86
Columns("A:H").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending,
Key2:=Range("E1") _
, Order2:=xlAscending, Key3:=Range("A1"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
** Sheets("PEPTRANSFILE-010807-090000").Select
** Sheets("PEPTRANSFILE-010807-090000").Name = "Buys"
Sheets("Buys").Select
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Sells"
Sheets("Sells").Select
Sheets.Add
Sheets("Sheet2").Select

How can I make the macro find the "active Sheet" I am on as opposed to
looking for some random named "PEPTRANSFILE-010807-090000" to re-name?

On Apr 18, 3:54 pm, JLGWhiz wrote:
** Sheets("PEPTRANSFILE-010807-090000").Select
** Sheets("PEPTRANSFILE-010807-090000").Name = "Buys"

Assuming that the sheet above is the active sheet:

ActiveSheet.Name = "Buys"

Should work.



"NewUser22" wrote:
I was hoping someone may be able to help me with a problem I am having
writing a macro. I hold my macros in a single excel file that I can
open to allow access to use of the macros. One macro in particular is
causing me some trouble because of this although the fix is probably
pretty simple. I receive an email everyday with an excel spreadsheet
that is out of whack. I am writing a macro that will allow me to
detele colums, arrange rows etc. The worksheet I receive by email
already has a name for the worksheet. I want to change the name of the
current active sheet "which is already named something different
everyday" to "buys" and add new sheets and change their names as well.
When I run the macro I get a runtime error 9 which I have concluded is
the fact that the macro is looking to change the name of worksheet
"XYZ" to "buy" when worksheet "XYZ" does not exist each time I run the
macro. Please see the Macro Below noting the stars next to the issues
at hand.

Columns("A:A").ColumnWidth = 16.43
Columns("E:E").ColumnWidth = 13.86
Columns("A:H").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending,
Key2:=Range("E1") _
, Order2:=xlAscending, Key3:=Range("A1"), Order3:=xlAscending,
Header:= _
xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
** Sheets("PEPTRANSFILE-010807-090000").Select
** Sheets("PEPTRANSFILE-010807-090000").Name = "Buys"
Sheets("Buys").Select
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Sells"
Sheets("Sells").Select
Sheets.Add
Sheets("Sheet2").Select

How can I make the macro find the "active Sheet" I am on as opposed to
looking for some random named "PEPTRANSFILE-010807-090000" to re-name?- Hide quoted text -

- Show quoted text -

Worked Like a charm, Thanks!