Activate sheet event
 

I have 3 sheets in a WB. When it opens, Sheet ("Master") is active. If I
move/copy a sheet from another WB into it, the new sheet is active. Is there
a way to re-activate Sheet ("Master"), perhaps in a Workbook event, after
the new sheet is added? A one -time event, so I can activate the new sheet
later and use it without the WB going back to Sheet ("Master") each time.
Thanks, Mike

Activate sheet event
 

Try this Mike

Set Ash = ActiveSheet
......
Ash.Select



Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim Ash As Worksheet
Application.ScreenUpdating = False
Set Ash = ActiveSheet
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\data\ron.xls")
Wb2.Sheets(1).Copy After:=Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Ash.Select
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Mike Fogleman" wrote in message ...
I have 3 sheets in a WB. When it opens, Sheet ("Master") is active. If I
move/copy a sheet from another WB into it, the new sheet is active. Is there
a way to re-activate Sheet ("Master"), perhaps in a Workbook event, after
the new sheet is added? A one -time event, so I can activate the new sheet
later and use it without the WB going back to Sheet ("Master") each time.
Thanks, Mike

Activate sheet event
 

Thanks Ron, but this is not quite what I had in mind. Both WBs are already
open. WB1 is going to analyze data from a different WB2 each week when we
receive it. I manually move a copy of WB2 sheet1 (named Sheet1") to the end
of WB1. WB1 now has 4 sheets and the added 4 th sheet is active. What I want
is when that 4th new sheet is added to WB1, it re-activates the sheet named
"Master" because that is where the command button is to begin analyzing the
new sheet. The new sheet is deleted from WB1 at the end of the process and
is ready for next weeks'. I have played with Workbook events, but NewSheet
will only work if the sheet is inserted from within WB1, and SheetActivate
will always kick me back to "Master" if I try to access the data in the new
sheet.
Any more thoughts on this?
TIA, Mike
"Ron de Bruin" wrote in message
...
Try this Mike

Set Ash = ActiveSheet
.....
Ash.Select



Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim Ash As Worksheet
Application.ScreenUpdating = False
Set Ash = ActiveSheet
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\data\ron.xls")
Wb2.Sheets(1).Copy After:=Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Ash.Select
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Mike Fogleman" wrote in message
...
I have 3 sheets in a WB. When it opens, Sheet ("Master") is active. If
I
move/copy a sheet from another WB into it, the new sheet is active. Is
there
a way to re-activate Sheet ("Master"), perhaps in a Workbook event,
after
the new sheet is added? A one -time event, so I can activate the new
sheet
later and use it without the WB going back to Sheet ("Master") each
time.
Thanks, Mike

Activate sheet event
 

I assume you run the analysis and then want to go back to the new sheet. Is
there some value in sheet4 that will indicate the analysis has been run?
You could test for this in sheetactivate event and jump out of the code if
the sheet index isn't 4 or if the sheet has this unique signature.

--
Regards,
Tom Ogilvy

Mike Fogleman wrote in message
...
Thanks Ron, but this is not quite what I had in mind. Both WBs are already
open. WB1 is going to analyze data from a different WB2 each week when we
receive it. I manually move a copy of WB2 sheet1 (named Sheet1") to the
end
of WB1. WB1 now has 4 sheets and the added 4 th sheet is active. What I
want
is when that 4th new sheet is added to WB1, it re-activates the sheet
named
"Master" because that is where the command button is to begin analyzing
the
new sheet. The new sheet is deleted from WB1 at the end of the process and
is ready for next weeks'. I have played with Workbook events, but NewSheet
will only work if the sheet is inserted from within WB1, and SheetActivate
will always kick me back to "Master" if I try to access the data in the
new
sheet.
Any more thoughts on this?
TIA, Mike
"Ron de Bruin" wrote in message
...
Try this Mike

Set Ash = ActiveSheet
.....
Ash.Select



Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim Ash As Worksheet
Application.ScreenUpdating = False
Set Ash = ActiveSheet
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\data\ron.xls")
Wb2.Sheets(1).Copy After:=Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Ash.Select
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Mike Fogleman" wrote in message
...
I have 3 sheets in a WB. When it opens, Sheet ("Master") is active.
If
I
move/copy a sheet from another WB into it, the new sheet is active. Is
there
a way to re-activate Sheet ("Master"), perhaps in a Workbook event,
after
the new sheet is added? A one -time event, so I can activate the new
sheet
later and use it without the WB going back to Sheet ("Master") each
time.
Thanks, Mike

Activate sheet event
 

Yes, that is a possibility because this sheet is an AS400 report dumped to
Excel when I get it. One of the first things the Analysis does is clean it
up with TRIM & VALUE formulas and delete blank rows. But then again, nothing
on the sheet is changed until you click the command button on the Master
sheet. Perhaps I could trigger from a variable being empty because it would
be at the time the sheet is added and have that take me back to the Master
sheet. Once I click the CB, the RowCount variable is never empty again until
the process is done and the extra sheet deleted.
"Tom Ogilvy" wrote in message
...
I assume you run the analysis and then want to go back to the new sheet.
Is
there some value in sheet4 that will indicate the analysis has been run?
You could test for this in sheetactivate event and jump out of the code if
the sheet index isn't 4 or if the sheet has this unique signature.

--
Regards,
Tom Ogilvy

Mike Fogleman wrote in message
...
Thanks Ron, but this is not quite what I had in mind. Both WBs are
already
open. WB1 is going to analyze data from a different WB2 each week when
we
receive it. I manually move a copy of WB2 sheet1 (named Sheet1") to the
end
of WB1. WB1 now has 4 sheets and the added 4 th sheet is active. What I
want
is when that 4th new sheet is added to WB1, it re-activates the sheet
named
"Master" because that is where the command button is to begin analyzing
the
new sheet. The new sheet is deleted from WB1 at the end of the process
and
is ready for next weeks'. I have played with Workbook events, but
NewSheet
will only work if the sheet is inserted from within WB1, and
SheetActivate
will always kick me back to "Master" if I try to access the data in the
new
sheet.
Any more thoughts on this?
TIA, Mike
"Ron de Bruin" wrote in message
...
Try this Mike

Set Ash = ActiveSheet
.....
Ash.Select



Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim Ash As Worksheet
Application.ScreenUpdating = False
Set Ash = ActiveSheet
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\data\ron.xls")
Wb2.Sheets(1).Copy After:=Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Ash.Select
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Mike Fogleman" wrote in message
...
I have 3 sheets in a WB. When it opens, Sheet ("Master") is active.
If
I
move/copy a sheet from another WB into it, the new sheet is active.
Is
there
a way to re-activate Sheet ("Master"), perhaps in a Workbook event,
after
the new sheet is added? A one -time event, so I can activate the new
sheet
later and use it without the WB going back to Sheet ("Master") each
time.
Thanks, Mike

Activate sheet event
 

I got it!

Public Rowcount (in a General code module)

In ThisWorkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = "Sheet1" And IsEmpty(RowCount) Then
Sheet1.Activate
End If
End Sub

Thanks Guys :)

"Mike Fogleman" wrote in message
...
Yes, that is a possibility because this sheet is an AS400 report dumped to
Excel when I get it. One of the first things the Analysis does is clean it
up with TRIM & VALUE formulas and delete blank rows. But then again,
nothing
on the sheet is changed until you click the command button on the Master
sheet. Perhaps I could trigger from a variable being empty because it
would
be at the time the sheet is added and have that take me back to the Master
sheet. Once I click the CB, the RowCount variable is never empty again
until
the process is done and the extra sheet deleted.
"Tom Ogilvy" wrote in message
...
I assume you run the analysis and then want to go back to the new sheet.
Is
there some value in sheet4 that will indicate the analysis has been run?
You could test for this in sheetactivate event and jump out of the code
if
the sheet index isn't 4 or if the sheet has this unique signature.

--
Regards,
Tom Ogilvy

Mike Fogleman wrote in message
...
Thanks Ron, but this is not quite what I had in mind. Both WBs are
already
open. WB1 is going to analyze data from a different WB2 each week when
we
receive it. I manually move a copy of WB2 sheet1 (named Sheet1") to
the
end
of WB1. WB1 now has 4 sheets and the added 4 th sheet is active. What
I
want
is when that 4th new sheet is added to WB1, it re-activates the sheet
named
"Master" because that is where the command button is to begin
analyzing
the
new sheet. The new sheet is deleted from WB1 at the end of the process
and
is ready for next weeks'. I have played with Workbook events, but
NewSheet
will only work if the sheet is inserted from within WB1, and
SheetActivate
will always kick me back to "Master" if I try to access the data in
the
new
sheet.
Any more thoughts on this?
TIA, Mike
"Ron de Bruin" wrote in message
...
Try this Mike

Set Ash = ActiveSheet
.....
Ash.Select



Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim Ash As Worksheet
Application.ScreenUpdating = False
Set Ash = ActiveSheet
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\data\ron.xls")
Wb2.Sheets(1).Copy After:=Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Ash.Select
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Mike Fogleman" wrote in message
...
I have 3 sheets in a WB. When it opens, Sheet ("Master") is
active.
If
I
move/copy a sheet from another WB into it, the new sheet is
active.
Is
there
a way to re-activate Sheet ("Master"), perhaps in a Workbook
event,
after
the new sheet is added? A one -time event, so I can activate the
new
sheet
later and use it without the WB going back to Sheet ("Master")
each
time.
Thanks, Mike