Marco Execution Question Please

[Bob Newman]

I have a very small macro I use with Excel 2000. The macro works fine when
I manually choose TOOLS/MACRO/MACROS/PLAY, when I try the keyboard shortcut
(CTRL Q) nothing happens (it has worked fine for many months). I tried
going in and changing the keyboard shortcut to some thing else and it still
doesn't work. Suggestions? (I am a novice - go easy on me).

 

[Dave Peterson]

Does your macro open another workbook?

If yes, then change your shortcut key to a lowercase letter (ctrl-q, not Q).

If you hold the shift key down when open a workbook, you disable the
auto_open/workbook_open code. And when you use a shortcut key that includes the
shift, excel gets confused and stops running the macro you just started.

Am I close?

 

[Bob Newman]

Macro does not open another workbook, and sorry for the typo, it IS a small
"q".

Bob

 

[Dave Peterson]

You may want to post the code or at least give a little more info.

Macro does not open another workbook, and sorry for the typo, it IS a small
"q".

Bob

 

[Bob Newman]

Coming right up:
***********************************************
Sub Log_2_Log()
'
' Log_2_Log Macro
' Macro recorded 12/29/2001 by
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Range("A1:I1").Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
End Sub
**********************************************************

 

[Dave Peterson]

It worked for me. And when it broke, it was because I was at the bottom of the
rows (65536) and I tried to select the cell one row below it.

You could add a few msgboxes to make sure you set up the shortcut key correctly.

This worked ok for me and has an added check to see if you're on the last row of
the worksheet.

Option Explicit
Sub Log_2_Log01()

Dim Rng2Copy As Range
Dim ToCell As Range

Set Rng2Copy = ActiveCell.Resize(1, 9)
Set ToCell = ActiveCell.End(xlDown)

If ToCell.Row = Rows.Count Then
MsgBox "At bottom of Worksheet!"
Exit Sub
Else
Set ToCell = ToCell.Offset(1, 0)
End If

Rng2Copy.Copy _
Destination:=ToCell

Application.CutCopyMode = False

ToCell.Offset(1, 0).Select

End Sub

Coming right up:
***********************************************
Sub Log_2_Log()
'
' Log_2_Log Macro
' Macro recorded 12/29/2001 by
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Range("A1:I1").Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
End Sub
**********************************************************

 

[Bob Newman]

What are msgboxes?

Thanks... Bob

It worked for me. And when it broke, it was because I was at the bottom of the
rows (65536) and I tried to select the cell one row below it.

You could add a few msgboxes to make sure you set up the shortcut key correctly.

This worked ok for me and has an added check to see if you're on the last row of
the worksheet.

Option Explicit
Sub Log_2_Log01()

Dim Rng2Copy As Range
Dim ToCell As Range

Set Rng2Copy = ActiveCell.Resize(1, 9)
Set ToCell = ActiveCell.End(xlDown)

If ToCell.Row = Rows.Count Then
MsgBox "At bottom of Worksheet!"
Exit Sub
Else
Set ToCell = ToCell.Offset(1, 0)
End If

Rng2Copy.Copy _
Destination:=ToCell

Application.CutCopyMode = False

ToCell.Offset(1, 0).Select

End Sub

 

[Dave Peterson]

It's a way of informing the user (or tester) that something's happening:

Option Explicit
Sub Log_2_Log01()

msgbox "Made it to the macro"

Dim Rng2Copy As Range
Dim ToCell As Range

Set Rng2Copy = ActiveCell.Resize(1, 9)
Set ToCell = ActiveCell.End(xlDown)

If ToCell.Row = Rows.Count Then
MsgBox "At bottom of Worksheet!"
Exit Sub
Else
Set ToCell = ToCell.Offset(1, 0)
End If

msgbox "About to copy"

Rng2Copy.Copy _
Destination:=ToCell

msgbox "just copied"

Application.CutCopyMode = False

ToCell.Offset(1, 0).Select

msgbox "Macro may have worked"

End Sub

What are msgboxes?

Thanks... Bob

<<snipped>>