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

 

[Michael M]

Did this on the train, so is UNTESTED

Sub MM1()
Dim ws As Worksheet
Set ws = Sheets("Sheet2")
With ws
.Columns("A:E").Copy
.Range("H1").PasteSpecial Paste:=xlPasteValues
.Range("$H$1:$L$5000").AutoFilter Field:=1, Criteria1:="="
.Range("H2").Select
.Range(Selection, Selection.End(xlDown)).ClearContents
.Range("$H$1:$L$5000").AutoFilter Field:=1
.Range("$H$1:$L$5000").AutoFilter Field:=2, Criteria1:="="
.Range("I2").Select
.Range(Selection, Selection.End(xlDown)).ClearContents
.Range("$H$1:$L$5000").AutoFilter Field:=2
.Range("H:H,I:I").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Range("H1").Value = " "
.Range("I1").Value = " "
With ws.Columns("H:L")
    .Value = .Value
End With
.Range("$H$1:$L$1048354").AutoFilter Field:=3, Criteria1:="="
.Range("H2:I2").Select
.Range(Selection, Selection.End(xlDown)).ClearContents
.Range("$H$1:$L$1048354").AutoFilter Field:=3
.Range("$H$1:$L$1048354").AutoFilter Field:=3, Criteria1:="="
.Range("$H$1:$L$1048354").AutoFilter Field:=4, Criteria1:="<>"
.Range("K2:L2").Select
.Range(Selection, Selection.End(xlDown)).ClearContents
.Range("$H$1:$L$1048354").AutoFilter Field:=4
.Range("$H$1:$L$1048354").AutoFilter Field:=3
.Range("H1").Value = "Group"
.Range("I1").Value = "GL"
End With
End Sub

 

[Fsailoh]

guys! i have tried all of your solutions and still the same issue!, please help.

 

[My Aswer Is This]

Your original post said:

or 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.

But the script you posted has nothing to do with deleting rows.

Please provide a example of a script your trying to run.

And explain in words what you want the script to do.
​

 

[Michael M]

when you run my code what happens...."doesn't work", provides no useful information for us to help further.
AND as @My Aswer Is This has pointed out, it would be easier to tell us what you are actually trying to.

 

[Fsailoh]

hi Michael, i am sorry i did not Notice your reply cause i did not look to the 2nd page.

anyway, what i am trying to do is to update a report in a sheet that is "very hidden" by clicking on a bottom in another sheet.

the problem is this report need to be modified by using (for example) data filter as you can see in the code, this report is generated from SAP system and that's why i need to modify it because SAP usually does not extract such report in an organized way.

regarding your code, it does work on the different sheet but it shows to me a debug in: ".Range("H1").Select "

 

[Michael M]

I don't see a
.Range("H1").Select
in your code....

 

[Fsailoh]

Did this on the train, so is UNTESTED

Sub MM1()
Dim ws As Worksheet
Set ws = Sheets("Sheet2")
With ws
.Columns("A:E").Copy
.Range("H1").PasteSpecial Paste:=xlPasteValues
.Range("$H$1:$L$5000").AutoFilter Field:=1, Criteria1:="="
.Range("H2").Select
.Range(Selection, Selection.End(xlDown)).ClearContents
.Range("$H$1:$L$5000").AutoFilter Field:=1
.Range("$H$1:$L$5000").AutoFilter Field:=2, Criteria1:="="
.Range("I2").Select
.Range(Selection, Selection.End(xlDown)).ClearContents
.Range("$H$1:$L$5000").AutoFilter Field:=2
.Range("H:H,I:I").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Range("H1").Value = " "
.Range("I1").Value = " "
With ws.Columns("H:L")
    .Value = .Value
End With
.Range("$H$1:$L$1048354").AutoFilter Field:=3, Criteria1:="="
.Range("H2:I2").Select
.Range(Selection, Selection.End(xlDown)).ClearContents
.Range("$H$1:$L$1048354").AutoFilter Field:=3
.Range("$H$1:$L$1048354").AutoFilter Field:=3, Criteria1:="="
.Range("$H$1:$L$1048354").AutoFilter Field:=4, Criteria1:="<>"
.Range("K2:L2").Select
.Range(Selection, Selection.End(xlDown)).ClearContents
.Range("$H$1:$L$1048354").AutoFilter Field:=4
.Range("$H$1:$L$1048354").AutoFilter Field:=3
.Range("H1").Value = "Group"
.Range("I1").Value = "GL"
End With
End Sub

check line number 8

 

[Michael M]

The line you pointed out is "H2" select....Not "H1"

Try this

Sub MM1()
Dim ws As Worksheet
Set ws = Sheets("Sheet2")
With ws
.Columns("A:E").Copy
.Range("H1").PasteSpecial Paste:=xlPasteValues
.Range("$H$1:$L$5000").AutoFilter Field:=1, Criteria1:="="
.Range(Cells(2, 8), Cells(2, 8).End(xlDown)).ClearContents
.Range("$H$1:$L$5000").AutoFilter Field:=1
.Range("$H$1:$L$5000").AutoFilter Field:=2, Criteria1:="="
.Range(Cells(2, 9), Cells(2, 9).End(xlDown)).ClearContents
.Range("$H$1:$L$5000").AutoFilter Field:=2
.Range("H:H,I:I").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Range("H1").Value = ""
.Range("I1").Value = ""
With ws.Columns("H:L")
    .Value = .Value
End With
.Range("$H$1:$L$1048354").AutoFilter Field:=3, Criteria1:="="
.Range(Cells(2, 8), Cells(2, 9).End(xlDown)).ClearContents
.Range("$H$1:$L$1048354").AutoFilter Field:=3
.Range("$H$1:$L$1048354").AutoFilter Field:=3, Criteria1:="="
.Range("$H$1:$L$1048354").AutoFilter Field:=4, Criteria1:="<>"
.Range(Cells(2, 11), Cells(2, 12).End(xlDown)).ClearContents
.Range("$H$1:$L$1048354").AutoFilter Field:=4
.Range("$H$1:$L$1048354").AutoFilter Field:=3
.Range("H1").Value = "Group"
.Range("I1").Value = "GL"
End With
End Sub