PDA

View Full Version : Short cut keys won't run entire macro



yeb4444
08-14-2007, 08:35 AM
Hello forum,
I'm trying to write a program to transfer data from several workbooks to one workbook. It seems to work perfect when I run the program from the VBA editor. However, as soon as I use a short cut key to run the program, it cuts out. Particulary, it stops immediately after the workbooks.open function. Anyone know what the problem is? Is there a way to fix this?
Thanks!
yeb4444

MsgBox() is never reached:

Workbooks.Open Filename:=Location_Files & Industry_Name & Update_Name_End
MsgBox (Check)

Bob Phillips
08-14-2007, 08:42 AM
Sounds like you have an unhandled error.

Show all the code.

yeb4444
08-14-2007, 08:49 AM
Sounds like you have an unhandled error.

Show all the code.


I'm afraid it is a fair amount of code, but here goes. It takes in the File Location, Save Location, update identifier and Industry Name from the excel work book. The right and left functions cut out quotation marks. Any ideas? The part that really confuses me is that it works if run from the editor.
Thanks!
David



Sub Make_Master_Main()
'
' This macro makes master data sheets for each industry listed on the input sheet.
' Make_Master_Main() 1) inputs the industry name from the input sheet and 2) runs Make_Master_RunAllFunctions() for that industry

' Application.ScreenUpdating = False 'turns off screen updating. Makes the Application run much faster
' Application.StatusBar = "Processing All Workbooks..."
'
Workbooks("Make Masters.xls").Activate 'activate the workbook associated with this macro
Sheets("Input Sheet").Activate 'activate the input sheet

Set FinalIndustryCell = Range("C65536").End(xlUp) 'Get the location of the bottom of the industries list

For Each Industry In Range("C10", FinalIndustryCell) 'For all cells in the industry list
Industry_Name = Right(Left(Industry.Value, Len(Industry.Value) - 1), Len(Industry.Value) - 2) 'imput and remove parentheses
Make_Master_RunAllFunctions (Industry_Name) 'Run all functions for that industry
Next

Application.ScreenUpdating = True 'turn screen updating back on
Application.StatusBar = False 'return the status bar to normal state

End Sub

Sub Make_Master_RunAllFunctions(Industry_Name)
'
'
'

Workbooks.Add ' Create a new workbook (the master workbook)
Set WS_Main = ActiveWorkbook ' Sets new workbook to WS_Main
WS_Main.Sheets.Add Count:=13 ' Adds 13 sheets to the new workbook for a total of 16 sheets
MasterFinalRow = 2 ' Sets FinalRow variable to 2 (because the first two rows are preset header rows)
Workbooks("Make Masters.xls").Activate 'Activate the Make Masters workbooks
Range("A65536").End(xlUp).Select
FinalRow = ActiveCell.Row

Location_Files = Right(Left(Cells(4, 1).Value, Len(Cells(4, 1).Value) - 1), Len(Cells(4, 1).Value) - 2)
MySaveAs_File = Right(Left(Cells(7, 1).Value, Len(Cells(7, 1).Value) - 1), Len(Cells(7, 1).Value) - 2) & Industry_Name & " Master.xls"
Update_Name_End = Cells(10, 1).Value
Update_Name_End = Right(Left(Update_Name_End, Len(Update_Name_End) - 1), Len(Update_Name_End) - 2)

Workbooks.Open Filename:=Location_Files & Industry_Name & Update_Name_End
MsgBox (Check)
...

yeb4444
08-14-2007, 08:57 AM
Sub Make_Master_Main()
'
' This macro makes master data sheets for each industry listed on the input sheet.
' Make_Master_Main() 1) inputs the industry name from the input sheet and 2) runs Make_Master_RunAllFunctions() for that industry

' Application.ScreenUpdating = False 'turns off screen updating. Makes the Application run much faster
' Application.StatusBar = "Processing All Workbooks..."
'
Workbooks("Make Masters.xls").Activate 'activate the workbook associated with this macro
Sheets("Input Sheet").Activate 'activate the input sheet

Set FinalIndustryCell = Range("C65536").End(xlUp) 'Get the location of the bottom of the industries list

For Each Industry In Range("C10", FinalIndustryCell) 'For all cells in the industry list
Industry_Name = Right(Left(Industry.Value, Len(Industry.Value) - 1), Len(Industry.Value) - 2) 'imput and remove parentheses
Make_Master_RunAllFunctions (Industry_Name) 'Run all functions for that industry
Next

Application.ScreenUpdating = True 'turn screen updating back on
Application.StatusBar = False 'return the status bar to normal state

End Sub

Sub Make_Master_RunAllFunctions(Industry_Name)
'
'
'

Workbooks.Add ' Create a new workbook (the master workbook)
Set WS_Main = ActiveWorkbook ' Sets new workbook to WS_Main
WS_Main.Sheets.Add Count:=13 ' Adds 13 sheets to the new workbook for a total of 16 sheets
MasterFinalRow = 2 ' Sets FinalRow variable to 2 (because the first two rows are preset header rows)
Workbooks("Make Masters.xls").Activate 'Activate the Make Masters workbooks
Range("A65536").End(xlUp).Select
FinalRow = ActiveCell.Row

Location_Files = Right(Left(Cells(4, 1).Value, Len(Cells(4, 1).Value) - 1), Len(Cells(4, 1).Value) - 2)
MySaveAs_File = Right(Left(Cells(7, 1).Value, Len(Cells(7, 1).Value) - 1), Len(Cells(7, 1).Value) - 2) & Industry_Name & " Master.xls"
Update_Name_End = Cells(10, 1).Value
Update_Name_End = Right(Left(Update_Name_End, Len(Update_Name_End) - 1), Len(Update_Name_End) - 2)

Workbooks.Open Filename:=Location_Files & Industry_Name & Update_Name_End
MsgBox (Check)

yeb4444
08-14-2007, 10:13 AM
got the problem - it appears to be with the hotkey I selected.