Help: Loop Macro

[Pumba]

Hi All,

I need your help

- I am trying to modify the macro such that it is enabled when any key stroke is made on any worksheet (I have about 7 different worksheets).
- I am trying to also modify the macro such that after "KeyCellsChanged" is run it returns the key cell reference to where the modification was made. I.e. if the modifciation was made on cell A1 then cells B29 - B31 is copied as per the rules defined in "KeyCellsChanged" but I want to revert the cursor back to A1


Sub auto_open()

' Run the macro DidCellsChange any time a entry is made in a
'cell in Cash Flow monthly
ThisWorkbook.Worksheets("Cash Flow monthly").OnEntry = "KeyCellsChanged"


End Sub


Sub KeyCellsChanged()
Application.Volatile
'
' KeyCellsChanged Macro
'

'
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

 

[Andrew Poulsom]

Instead of using OnEntry use the Workbook_SheetChange in the module for ThisWorkbook. Then avoid any Selects in your code. For example this:

Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

can be written as:

Range(Selection, Selection.End(xlToRight)).Copy
Range("B31").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Pumba]

Hi andrew,

Thanks for the prompt reply:

I have managed to edit the macro to look like this:

Sub auto_open()

End Sub


Sub KeyCellsChanged()
Application.Volatile
'
' KeyCellsChanged Macro
'

'
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B29").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Range("B31").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

What happnes now is that values changed on other worksheets enable the macro key cells changed to run however the cursor doesn't moves to the last keystroke relating to the macro i.e. cells/row B29-B31. ideally if a change was made in cell A1 I don't want the macro to move the cursor to B29 for example, I want it to remain in cell A1 but run the macro when something was changed.

Sorry Andrew - I didn't quite understand what your macro was enabling me to do?

 

[Andrew Poulsom]

If you avoid selecting the Selection won't change.