Workbook.Activate / Window.Activate problem

OK. I give up. Someone smarter than me is going to need to tell me
why I am deleting the worksheet from the wrong workbook. Seems like I
can't figure out how to correctly shift from one to another. The last
statement here gives me a 'subscript out of range' error, and I can't
figure out why.

Code follows:

Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal
Target As Range)
Dim cell As Range
Dim wrksht As Worksheet
Dim SendMail As Outlook.MailItem
Dim i%, ReportPage$, ReportRow%, Subject$, SelectedAddresses$,
ScheduleName$, IndividualName$

If sh.Name Like "####Q#" Then
If Target.Row <= 2 And Cells(Target.Row, Target.Column).Value <>
"" Then
If MsgBox("Generate report for " & Range(sh.Name & "!" &
Cells(1, Target.Column).Address).Value & " " & Range(sh.Name & "!" &
Cells(2, Target.Column).Address).Value & "?", vbYesNo) = vbYes Then
ScheduleName$ = Left(ActiveWorkbook.Name,
Len(ActiveWorkbook.Name) - 4)
IndividualName$ = ScheduleName$ & " " & Range(sh.Name & "!"
& Cells(1, Target.Column).Address).Value & " " & Range(sh.Name & "!" &
Cells(2, Target.Column).Address).Value
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=IndividualName$
Workbooks(ScheduleName$ & ".xls").Activate
Sheets(Array(Left(ActiveSheet.Name, 5) & "1",
Left(ActiveSheet.Name, 5) & "2", Left(ActiveSheet.Name, 5) & "3",
Left(ActiveSheet.Name, 5) & "4")).Copy
before:=Workbooks(IndividualName$ & ".xls").Sheets(1)
Workbooks(IndividualName$ & ".xls").Activate
' Windows("CPF Time Off Schedule V2 Mia
Bijaksana.xls").Activate
'Sheets("Sheet1").Delete
Workbooks(ScheduleName$ & ".xls").Activate
Windows(ScheduleName$ & ".xls").Activate
Workbooks(IndividualName$ & ".xls").Activate
Windows(IndividualName$ & ".xls").Activate

First, I'm kind of surprised you use the _sheetselectionchange event. But
that's your choice.

But the nice thing about that is that there are things passed to that
routine--the sheet (as sh) and the range (as target).

And since you're in the ThisWorkbook module, you can use the Me. keyword to
refer to the workbook with the code. And by using those variables, you can kind
of slim down your code.

I _think_ that this does the same as you had before--but double check it.

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As
Range)

Dim IndividualName As String

Dim NewWkbk As Workbook
Dim shPrefix As String

shPrefix = Left(sh.Name, 5)

If UCase(sh.Name) Like "####Q#" Then
If Target.Row <= 2 And Target.Value <> "" Then
If MsgBox("Generate report for " _
& sh.Name & "!" & sh.Cells(1, Target.Column).Value & " " _
& sh.Name & "!" & sh.Cells(2, Target.Column).Value _
& "?", vbYesNo) = vbYes Then

Set NewWkbk = Workbooks.Add(1) 'single sheet in the newworkbook
NewWkbk.Worksheets(1).Name = "Dummy" 'delete it later

Me.Sheets(Array(shPrefix & "1", shPrefix & "2", _
shPrefix & "3", shPrefix & "4")).Copy _
before:=NewWkbk.Sheets(1)

Application.DisplayAlerts = False
NewWkbk.Worksheets("dummy").Delete
Application.DisplayAlerts = True

IndividualName _
= Left(Me.FullName, Len(Me.FullName) - 4) _
& " " & sh.Cells(1, Target.Column).Value & " " _
& sh.Cells(2, Target.Column).Value

NewWkbk.SaveAs Filename:=IndividualName

'me.activate 'or stay in the new workbook?
End If
End If
End If

End Sub

=====
One of the problems with your:
Sheets("Sheet1").Delete
is that it's unqualified.

You didn't tell it what workbook Sheet1 belonged to. In a general module, that
"sheets("sheet1")" would refer to the activeworkbook.

But behind ThisWorkbook, excel figures anything unqualified belongs to the thing
that owns that module--in this case the workbook with the code. And since you
didn't have a worksheet named sheet1 in that workbook--you heard the big
kaboooom!



Tim wrote:
>
> OK. I give up. Someone smarter than me is going to need to tell me
> why I am deleting the worksheet from the wrong workbook. Seems like I
> can't figure out how to correctly shift from one to another. The last
> statement here gives me a 'subscript out of range' error, and I can't
> figure out why.
>
> Code follows:
>
> Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal
> Target As Range)
> Dim cell As Range
> Dim wrksht As Worksheet
> Dim SendMail As Outlook.MailItem
> Dim i%, ReportPage$, ReportRow%, Subject$, SelectedAddresses$,
> ScheduleName$, IndividualName$
>
> If sh.Name Like "####Q#" Then
> If Target.Row <= 2 And Cells(Target.Row, Target.Column).Value <>
> "" Then
> If MsgBox("Generate report for " & Range(sh.Name & "!" &
> Cells(1, Target.Column).Address).Value & " " & Range(sh.Name & "!" &
> Cells(2, Target.Column).Address).Value & "?", vbYesNo) = vbYes Then
> ScheduleName$ = Left(ActiveWorkbook.Name,
> Len(ActiveWorkbook.Name) - 4)
> IndividualName$ = ScheduleName$ & " " & Range(sh.Name & "!"
> & Cells(1, Target.Column).Address).Value & " " & Range(sh.Name & "!" &
> Cells(2, Target.Column).Address).Value
> Workbooks.Add
> ActiveWorkbook.SaveAs Filename:=IndividualName$
> Workbooks(ScheduleName$ & ".xls").Activate
> Sheets(Array(Left(ActiveSheet.Name, 5) & "1",
> Left(ActiveSheet.Name, 5) & "2", Left(ActiveSheet.Name, 5) & "3",
> Left(ActiveSheet.Name, 5) & "4")).Copy
> before:=Workbooks(IndividualName$ & ".xls").Sheets(1)
> Workbooks(IndividualName$ & ".xls").Activate
> ' Windows("CPF Time Off Schedule V2 Mia
> Bijaksana.xls").Activate
> 'Sheets("Sheet1").Delete
> Workbooks(ScheduleName$ & ".xls").Activate
> Windows(ScheduleName$ & ".xls").Activate
> Workbooks(IndividualName$ & ".xls").Activate
> Windows(IndividualName$ & ".xls").Activate

--

Dave Peterson

Thanks Dave! Very helpful. Have never understood or used 'me' before,
and now I understand the need for qualification in this project. There
was one innocent looking thing you changed:

Cells(Target.Row, Target.Column).Value

to:

Target.Value

Seems reasonable, and works when target.value = "", but crashes when
target.value = "Someones Name" with a type mismatch.

I'm guessing that it wasn't the change that caused the trouble. I'm guessing
that it was because you selected more than one cell.

If that's the case, you could tell the code to just drop out if the user selects
more than one cell:

Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, _
ByVal Target As Range)

Dim IndividualName As String

Dim NewWkbk As Workbook
Dim shPrefix As String

if target.cells.count > 1 then exit sub

shPrefix = Left(sh.Name, 5)
'rest of code here....

=========
Or you could just look at the first cell in that selection.

If Target.Row <= 2 And Target.cells(1,1).Value <> "" Then

=========

Post back if I guessed wrong...

Tim wrote:
>
> Thanks Dave! Very helpful. Have never understood or used 'me' before,
> and now I understand the need for qualification in this project. There
> was one innocent looking thing you changed:
>
> Cells(Target.Row, Target.Column).Value
>
> to:
>
> Target.Value
>
> Seems reasonable, and works when target.value = "", but crashes when
> target.value = "Someones Name" with a type mismatch.

--

Dave Peterson