How do I Unhide, Refresh Query and Re-hide rows in VBA???

[Jeevz_87]

Hi All,


Another stumbling block on my road to VBA proficiency! This is a weird one.

I'm currently stuck trying to run a macro sheet 2 from sheet 1. I only want the macro to execute in sheet 2 (sheet 1 should stay unaffected). Furthermore, when I run the macro, I want to remain on the active sheet (sheet 1) and not be taken to sheet 2 when i run this macro.

I'm using the call feature to run my macros at the moment to save time and current using the following in sheet 1 (super simple);

Sub run_macro_in_sheet2()

Sheets("Sheet 2").Select
Call Do_All (name of macro)

End Sub

Is there a better way to do this?

Kind regards,


Jeevz

 

[mustacheman]

If the function you created Do_All has vba script to go to another worksheet, then it's going to move to other sheets. You should put extra code within that function that references the sheet you want the code to be or stay on.

If you want the vba script to go back to the original sheet, then add another line to reference back to sheet1 or sheet2 at the end, depending where you want the code.

 

[Jeevz_87]

How would that look if I referenced the original sheet within the code in this case?

 

[mustacheman]

Sheets("Sheet 2").Select
Sheets("Sheet 1").Select

This coding is correct but you just need to put it at the beginning and the ends of your code. The code will follow the exact steps you want. So if you want the code to run on sheet 1 then reference sheet 1 first.

Say sheet1.select
then call function

Then if you want the sheet to go back to sheet 2 after. Add another sheet2.select after your call statement.

I hope that helps.

 

[Domenic]

Try something like this . . .

Sub run_macro_in_sheet2()

    Call Do_All(Sheets("Sheet 2")) '<--- pass your worksheet to Do_All

End Sub

Sub Do_All(ByVal ws As Worksheet) '<--- parameter that accepts a worksheet object

    '1) Refer to your workheet in this procedure using the variable ws.
   
    '2) Usually there's no need to select your worksheet before performing an operation.
   
    'Your code here
    '
    '
    '
   
End Sub

Actually, when calling another procedure, there's no need to use the keyword Call. The following would suffice . . .

Sub run_macro_in_sheet2()

    Do_All Sheets("Sheet 2") '<--- pass your worksheet to Do_All without brackets

End Sub

Hope this helps!