Enable macro on other worksheet

[Pumba]

Hi There

I am nearly there but need your assistance I have a deadline of 12pm and am running out of ideas!!!

I am trying to define the macro entitled "interest" so that it only operates on the work sheet "cash flow monthly" i.e. an input can be made on other worksheets which enables the macro but the macro ONLY runs on the worksheet "cash flow monthly"

Also I am trying to ensure that the cursor moves back to where the ammendement was made i.e. if the cell ammendement was made in cell A1 the macro should run but the cursor should remain in cell A1.

Your help would be much appericated

Thanks





Sub Workbook_Open()

'Run Macro

ThisWorkbook.Worksheets("Cash Flow monthly").OnEntry = "interest"
ThisWorkbook.Worksheets("P&L Monthly").OnEntry = "interest"
ThisWorkbook.Worksheets("Balance Sheet").OnEntry = "interest"
ThisWorkbook.Worksheets("Nominal Revenue Comparison").OnEntry = "interest"

End Sub

Sub interest()
'
' interest 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
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

 

[RoryA]

As far as I can see your macro repeatedly copies and pastes exactly the same area so can you clarify what it is supposed to do?

 

[Pumba]

Hi Rorya,

The macro "interest" does copy and paste repeatidly - however I want it to copy and paste Only in worksheet "Cash Flow Balance". I have initialised the macro on each worksheet by usign the worksheet open function but can't define the "interest" macro such that it only operates on the specific worksheet cash flow balance.

Your help is much apperciated!

 

[RoryA]

If it only operates on one sheet, what is the point of repeatedly copying and pasting the same range??