ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   WHO CAN EXPLAIN THAT "macro problem"? (https://www.excelbanter.com/excel-programming/387416-who-can-explain-macro-problem.html)

GorKo
WHO CAN EXPLAIN THAT "macro problem"?
 
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

JE McGimpsey
WHO CAN EXPLAIN THAT "macro problem"?
 
It might be helpful to post the code in a reply...

In article .com,
"GorKo" wrote:

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

GorKo
WHO CAN EXPLAIN THAT "macro problem"?
 
On Apr 14, 1:09 am, JE McGimpsey wrote:
It might be helpful to post the code in a reply...

In article .com,

"GorKo" wrote:
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

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

Barb Reinhardt
WHO CAN EXPLAIN THAT "macro problem"?
 
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" wrote:

On Apr 14, 1:09 am, JE McGimpsey wrote:
It might be helpful to post the code in a reply...

In article .com,

"GorKo" wrote:
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

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

GorKo
WHO CAN EXPLAIN THAT "macro problem"?
 
On Apr 14, 3:54 pm, Barb Reinhardt
wrote:
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" wrote:
On Apr 14, 1:09 am, JE McGimpsey wrote:
It might be helpful to post the code in a reply...

In article .com,

"GorKo" wrote:
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

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

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

Carl Hartness[_2_]
WHO CAN EXPLAIN THAT "macro problem"?
 
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.

On Apr 14, 5:29 pm, "GorKo" wrote:
On Apr 14, 3:54 pm, Barb Reinhardt



wrote:
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" wrote:
On Apr 14, 1:09 am, JE McGimpsey wrote:
It might be helpful to post the code in a reply...

In article .com,

"GorKo" wrote:
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

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

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

GorKo
WHO CAN EXPLAIN THAT "macro problem"?
 
On Apr 15, 12:06 pm, "Carl Hartness" wrote:
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.

On Apr 14, 5:29 pm, "GorKo" wrote:

On Apr 14, 3:54 pm, Barb Reinhardt

wrote:
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" wrote:
On Apr 14, 1:09 am, JE McGimpsey wrote:
It might be helpful to post the code in a reply...

In article .com,

"GorKo" wrote:
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

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

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

Thanks that maybe it.


All times are GMT +1. The time now is 02:28 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com