Run the same VBA Script on multiple sheets in same workbook

  • Hello Everyone,


    I'm new to VBA scripting world in Excel, through searching and learning from online forums and a freind's help I was able successfully apply the VBA codes below the first worksheet.


    So now I'm trying to run the same code on an Excel Workbook that has multiple Worksheets and I would like to apply VBA script to all Worksheets in one shot.


    Here is my code.



    I will eventually would like to run this VBA script from a batch file to update all the relevant worksheets within the workbook.


    Thank you in advance for your assistance and help !!


    Take care,


    TD

  • Re: Run the same VBA Script on multiple sheets in same workbook


    Try this modified code


  • Re: Run the same VBA Script on multiple sheets in same workbook


    Hi TD,


    Welcome to Ozgrid!!


    Though your macro can be written more simply, this will run it in on each sheet in the workbook:



    Regards,


    Robert

  • Re: Run the same VBA Script on multiple sheets in same workbook


    Hi Alan,


    This is wonderful, runs like a charm :) Thank you !!


    Is there anyway to run this VBA script via a Batch file?


    I have a program that can execute this batch file every morning to update the XLSX file with these changes that the VBA codes are doing.


    Also, could this file be saved and closed after the VBA codes have been applied?


    Thank you very much once again,


    TD

  • Re: Run the same VBA Script on multiple sheets in same workbook


    Hi Robert,


    Another great solution :)


    Thank you, for some reasons this code took longer than Alan's code to run but it ran and provided the expected results.


    I wanted to see if workbook can be saved and closed after the codes have been applied.


    Thank you sir !!


    TD

  • Re: Run the same VBA Script on multiple sheets in same workbook


    Quote


    Also, could this file be saved and closed after the VBA codes have been applied?

    Do you want this added to the code?


    To get the program to run automatically, employ the Windows Scheduler and have it run the macro at a specific time.


    https://technet.microsoft.com/…ry/cc766428(v=ws.11).aspx

  • Re: Run the same VBA Script on multiple sheets in same workbook


    Hi Alan,


    Yes please.


    So at the end of the day I would like to have a batch file that can run the VBA script that updates the worksheets within a xlsx file and then closes it once done.


    Still searching the web to get all the answers.


    Thank you Alan,


    TD

  • Re: Run the same VBA Script on multiple sheets in same workbook


    Hi Alan,


    I tried the whole code and received an Compile Error of Wrong number of Arguments or invalid property assigned.


    Thank you,


    TD


  • Re: Run the same VBA Script on multiple sheets in same workbook


    Hi Alan,


    One I changed the


    Application.Workbooks.Close SaveChanges:=True

    To
    Application.ThisWorkbook.Close SaveChanges:=
    True


    it worked.


    Thank you for all your help.


    TD


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!