Activate sheet event

[Mike Fogleman]

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

 

[Ron de Bruin]

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

 

[Mike Fogleman]

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

 

[Tom Ogilvy]

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.

 

[Mike Fogleman]

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.

 

[Mike Fogleman]

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