Shortcut key not running macro properly

[Guest]

Running Excel 2007 under WinXPPro

I wrote four macros. The first three each do a specific funtion and are
named NameStateSum1, 2 and 3. The fourth one is named NameStateSum (no
suffix) and it simply runs the three numbered macros in order. The parent
macro is set up to run on CNTL+SHFT+N

If I run the parent macro from the developer tab, it runs fine. When I try
to run it with the shortcut keys, it executes only the first of three
submacros and stops.

Any idea what's causing this and how I can make it work with the shortcut
keys?

 

[Guest]

it would help if you show the macro code....

 

[Guest]

Here's the macro code:

Sub NameStateSum()
'
' NameStateSum Macro
' This macro runs the three submacros to create and format a pivot table
form the Names and Addresses workbook.
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Application.Run "PERSONAL.XLSB!NameStateSum1"
Application.Run "PERSONAL.XLSB!NameStateSum2"
Application.Run "PERSONAL.XLSB!NameStateSum3"
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose
End Sub

Sub NameStateSum1()
'
' NameStateSum1 Macro
' This macro will open the Names and Addresses workbook with the xource data
for PivotTable.
'

'
ChDir _
"C:\Documents and Settings\Ted Horsch\Desktop\MentorTraining
Exercise Files"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Ted Horsch\Desktop\MentorTraining
Exercise Files\Names and Addresses.xlsx"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Ted Horsch\Desktop\MentorTraining
Exercise Files\Summary by State.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Sub NameStateSum2()
'
' NameStateSum2 Macro
' This macro creates a PivotTable of employee last names by state and counts
the employees in each state.
'

'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C:\Documents and Settings\Ted Horsch\Desktop\MentorTraining
Exercise Files\Names and Addresses.xlsx!ClientList" _
, Version:=xlPivotTableVersion12).CreatePivotTable
TableDestination:= _
"Sheet1!R1C1", TableName:="PivotTable3", DefaultVersion:= _
xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("LastName")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("State")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("FirstName"), "Count of FirstName", xlCount
ActiveSheet.PivotTables("PivotTable3").TableStyle2 = "PivotStyleLight22"
ActiveSheet.PivotTables("PivotTable3").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("PivotTable3").RowGrand = False
End Sub

Sub NameStateSum3()
'
' NameStateSum3 Macro
' This macro renames the worksheet and saves the file.
'

'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Summary by State"
ActiveWorkbook.Save
End Sub

 

[Dave Peterson]

If you open a workbook with the shift key held down, then excel thinks you don't
want to run the auto_open or workbook_open procedures.

And since your shortcut key includes the shift key, it's confusing excel and
excel thinks you want to stop.

Remove the shift key from your shortcut key and try it out.

 

[Guest]

I LOVE this discussion group! Dave, thanks a million for solving my problem
for me.

 

[cla99009]

I've discovered a possible work-around to this problem. If you have a user
form that the macro calls prior to the open workbook command, it breaks the
shift problem and will continue to run the macro after the file opens.