Call macro (Sheet 1) from any change in sheet 2

**mark02** · 12-12-2012, 09:46 PM

Hi folks,

I have the following target:
I got a macro in sheet 1, which I want to trigger after a change in sheet 2.

My idea to do this is my a sub in sheet 2:

Private Sub Worksheet_Change(ByVal Target As Range)
Call Markmacro
End Sub

Which is referring to:

Sub Markmacro()

Range("D3:E3").Select
Selection.Copy
lMaxRows = Cells(Rows.Count, "G").End(xlUp).Row
Range("G" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

If I place the last sub in a module it works, only it works in sheet 2 i.s.o. sheet 1.

Googling my issues directed me to multiple forums on this topic which offer this solution:

Private Sub Worksheet_Change(ByVal Target As Range)
Call Sheet1.Markmacro
End Sub

Somehow the sheet1. additive is not working (run-time error 1004, application defined or object defined error).

I know my mistake is probably very stupid, which is frustrating me enormous.

Thanks in advance for any help,

Cheers 'n Beers - Mark

**Jakobshavn** · 12-12-2012, 09:59 PM

I would leave Markmacro in a standard module. When it is called from the Event Macro, it will attempt to do the copy/paste on the Active sheet (sheet2). If you want to do the copy/paste on sheet1, you should make that explicit within Markmacro.

**mark02** · 12-12-2012, 10:05 PM

HI Jakobshavn,

Thanks for your quick reply.

I thought of that option as well, but I fail to make that explicit.

Might be worthwhile to mention that I am a noob in VBA.

C&B, Mark

**Jakobshavn** · 12-12-2012, 10:14 PM

Within the Sheet2 code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Call Markmacro
Application.EnableEvents = True
End Sub

Within the standard module:

Sub Markmacro()
Sheets("Sheet1").Activate
Range("D3:E3").Select
Selection.Copy
lMaxRows = Cells(Rows.Count, "G").End(xlUp).Row
Range("G" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet2").Activate
End Sub

Make sure I have not "broken" the spelling of the sheet names.