How can i run MACROS in different sheet?

[Fsailoh]

hi,

i am developing an excel file that use the MACROS, i have an issue which is how can i run a macro in sheet2 from an object in sheet1?

for example, i have a bottom to click on it in sheet1 that will run the MACROS and delete the rows in sheet2, the issue is that when i start this MACROS the rows that will be deleted going to be in sheet1 not sheet2.

thanks

 

[Norie]

All you need to do is make sure your code references Sheet2 correctly throughout.

 

[Fsailoh]

This is the CODE that I have:

Columns("A:E").Select
Selection.Copy
Range("H1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
activesheet.Range("$H$1:$L$5000").AutoFilter Field:=1, Criteria1:="="
Range("H2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
activesheet.Range("$H$1:$L$5000").AutoFilter Field:=1
activesheet.Range("$H$1:$L$5000").AutoFilter Field:=2, Criteria1:="="
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
activesheet.Range("$H$1:$L$5000").AutoFilter Field:=2
Range("H:H,I:I").Select
Range("I1").Activate
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("H1").Select
ActiveCell.FormulaR1C1 = " "
Range("I1").Select
ActiveCell.FormulaR1C1 = " "
Columns("H:L").Select
Columns("H:L").Copy
Columns("H:L").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("J2").Select
activesheet.Range("$H$1:$L$1048354").AutoFilter Field:=3, Criteria1:="="
Range("H2:I2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
activesheet.Range("$H$1:$L$1048354").AutoFilter Field:=3
activesheet.Range("$H$1:$L$1048354").AutoFilter Field:=3, Criteria1:="="
activesheet.Range("$H$1:$L$1048354").AutoFilter Field:=4, Criteria1:="<>"
Range("K2:L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
activesheet.Range("$H$1:$L$1048354").AutoFilter Field:=4
activesheet.Range("$H$1:$L$1048354").AutoFilter Field:=3
Range("H1").Select
ActiveCell.FormulaR1C1 = "Group"
Range("I1").Select
ActiveCell.FormulaR1C1 = "GL"
Range("I2").Select

 

[Norie]

Everywhere that you have Range(...) refers to a range on the active sheet, you need to add sheet references throughout the code to ensure its working on the correct sheet.

For example, this code will run on Sheet2.

With Sheets("Sheet2")
    .Columns("A:E").Copy
    .Range("H1").PasteSpecial Paste:=xlPasteValues
End With

 

[Fsailoh]

i could not understand, could you explain more please?

 

[Ramballah]

Private Sub CommandButton1_Click()CommandButton1.BackColor = 858083
    Call Sheet4.CopyToCorrectRanges
    
    Call Sheet2.AddCorrectRanges
End Sub

This is copied from my own sheet. On Sheet 1 i have this button and if i click it it will activate the macros on Sheet 2 and sheet 4. Please see how i did this and experiment yourself !
Just the Call Sheet2.[sheetname] part. no need for the Commandbutton1 thats just me wanting to color my button lol.

 

[Fsailoh]

it does not work, could you please give me an example on my Code?

 

[Ramballah]

it does not work, could you please give me an example on my Code?

I assume you have your code in Sheet2. And you want a button in sheet1 that activates the code in sheet2 right?
So the macro you give to your button should be:

Private Sub whatever()
Call Sheet2.[macroname]
End Sub

You didn't give us the name of your macro so i cant put it directly for you.

 

[Fsailoh]

it does not work!

it gave me this error:

Run-time error '1004':
Application-defined or object-defined error

 

[My Aswer Is This]

It's always best when writing scripts to specify sheet names and not use activesheet.

Like Sheets("Mom").column('F").copy