Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > WHO CAN EXPLAIN THAT "macro problem"?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,624
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 63
Default 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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 18
Default 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.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Some users don't need the "message space" to explain their issues. XLFanatico Excel Discussion (Misc queries) 11 March 16th 09 04:53 PM
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
problem with Linking workbooks via "copy" and "paste link" Arkitek Excel Discussion (Misc queries) 0 December 19th 06 10:03 PM
Problem: Worksheets("New Style 2006").Unprotect Password:="naPrint" Karoo News[_2_] Excel Programming 1 January 30th 06 02:40 PM


All times are GMT +1. The time now is 08:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement