WHO CAN EXPLAIN THAT "macro problem"?

  • Thread starter Thread starter GorKo
  • Start date Start date
G

GorKo

I created a macro in excel that has a shortcut assigned activating it,
Ctrl+Shift+A.
Macro runs fine when it is invoked using Menu: Tools\Macros\...\Run.
But when I use a shortcut it never finishes just dies.

Please Help

Georgee
 
It might be helpful to post the code in a reply...
Click to expand...

The Code opens second file copies range to the first file, closes
second file and performs some data manipulations.

when I activate it with my shortcut Ctrl+Shift+A it only opens second
file and dies.

Here it comes:

Sub RRSamedy()
'
' RRSamedy Macro
' Macro recorded 4/12/2007 by dispatch
'


'Import data from Route-C

Application.DisplayAlerts = False


Workbooks.Open Filename:="Y:\ROUTE-C.xls", UpdateLinks:=0,
Notify:=False
Sheets("SD").Select


Range("B26:N55").Select
Selection.Copy
Windows("PPlog.xls").Activate
Sheets("RRSD").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Windows("ROUTE-C.xls").Activate

'Close ROUTE-C

ActiveWindow.Close

Application.DisplayAlerts = True

'Sort RRSD Tab
Sheets("RRSD").Select
Range("B2:N30").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

'Transfer Time, Order#, Customer Name
Sheets("RRSD").Select
Range("A2:C30").Select
Selection.Copy
Sheets("List").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Paste").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'Transfer City, State, Zip

Sheets("RRSD").Select
Range("D2:F30").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("List").Select
Range("N2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False



Sheets("RRSD").Select
Range("A1").Select

Sheets("List").Select
Range("A1").Select


End Sub
 
You seem to be moving back and forth between workbooks, but it's not clear if
the workbooks are even open. Do you get an error when you get to one of the
workbook.activate lines? I'd step through this line by line to see what's
up.

I'd add the following:
Dim aWB as workbook
Dim oWB as workbook

set awb = activeworkbook 'I'm presuming this is the workbook PPLOG.xls

On Error Resume Next
Set oWB = Nothing
Set oWB = Workbooks.Open(Filename:="Y:\ROUTE-C.xls", UpdateLinks:=0,
Notify:=False)
On Error GoTo 0

If Not oWB Is Nothing Then
oWB.Sheets("SD").Range("B26:N55").Copy
'blah blah blah
End If

You can refer to aWB the same way.
 
You seem to be moving back and forth between workbooks, but it's not clear if
the workbooks are even open. Do you get an error when you get to one of the
workbook.activate lines? I'd step through this line by line to see what's
up.

I'd add the following:
Dim aWB as workbook
Dim oWB as workbook

set awb = activeworkbook 'I'm presuming this is the workbook PPLOG.xls

On Error Resume Next
Set oWB = Nothing
Set oWB = Workbooks.Open(Filename:="Y:\ROUTE-C.xls", UpdateLinks:=0,
Notify:=False)
On Error GoTo 0

If Not oWB Is Nothing Then
oWB.Sheets("SD").Range("B26:N55").Copy
'blah blah blah
End If

You can refer to aWB the same way.

GorKo said:
It might be helpful to post the code in a reply...
Click to expand...

Thanks I will keep in mind this comments but my only concern was
that this macro in the same starting condition would go through if
invoked by menu
or would stop after opening the second workbook using shorcut Ctrl
+Shift+A,
now I have another observation, I changed the shortcut to Ctrl+a and
it works just fine,
I should be happy but I just still try to understand what is the logic
behind it???

George
Click to expand...
Click to expand...
 
Is it possible that Ctrl+Shift+A points someplace unexpected, like a
partial debug macro (set a breakpoint to ensure the right macro is
executing), or that the second file also has a Ctrl+Shift+A macro?
When I open two files with the same macros, and in my case use Ctrl
+Shift+T, the macro of the first opened file runs regardless of which
file is active, so I have to carefully specify ActiveWorkbook or
ThisWorkbook to make sure I get the right action.

I steer clear of using combinations that are previously defined.
There is a Ctrl+A that I like. When the macro hangs, you might go
through Tools->Macro->Macros and check the options to see which file
has Shift+Ctrl+A.

Carl.

You seem to be moving back and forth between workbooks, but it's not clear if
the workbooks are even open. Do you get an error when you get to one of the
workbook.activate lines? I'd step through this line by line to see what's
up.
Click to expand...
I'd add the following:
Dim aWB as workbook
Dim oWB as workbook
Click to expand...
set awb = activeworkbook 'I'm presuming this is the workbook PPLOG.xls
Click to expand...
On Error Resume Next
Set oWB = Nothing
Set oWB = Workbooks.Open(Filename:="Y:\ROUTE-C.xls", UpdateLinks:=0,
Notify:=False)
On Error GoTo 0
Click to expand...
If Not oWB Is Nothing Then
oWB.Sheets("SD").Range("B26:N55").Copy
'blah blah blah
End If
Click to expand...
You can refer to aWB the same way.
Click to expand...
Click to expand...
 
Is it possible that Ctrl+Shift+A points someplace unexpected, like a
partial debug macro (set a breakpoint to ensure the right macro is
executing), or that the second file also has a Ctrl+Shift+A macro?
When I open two files with the same macros, and in my case use Ctrl
+Shift+T, the macro of the first opened file runs regardless of which
file is active, so I have to carefully specify ActiveWorkbook or
ThisWorkbook to make sure I get the right action.

I steer clear of using combinations that are previously defined.
There is a Ctrl+A that I like. When the macro hangs, you might go
through Tools->Macro->Macros and check the options to see which file
has Shift+Ctrl+A.

Carl.

You seem to be moving back and forth between workbooks, but it's not clear if
the workbooks are even open. Do you get an error when you get to one of the
workbook.activate lines? I'd step through this line by line to see what's
up.
I'd add the following:
Dim aWB as workbook
Dim oWB as workbook
set awb = activeworkbook 'I'm presuming this is the workbook PPLOG.xls
On Error Resume Next
Set oWB = Nothing
Set oWB = Workbooks.Open(Filename:="Y:\ROUTE-C.xls", UpdateLinks:=0,
Notify:=False)
On Error GoTo 0
If Not oWB Is Nothing Then
oWB.Sheets("SD").Range("B26:N55").Copy
'blah blah blah
End If
You can refer to aWB the same way.
:
It might be helpful to post the code in a reply...
Click to expand...

Thanks that maybe it.
Click to expand...
Click to expand...
 
Back
Top