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.

Sub copy_Cell_A4()
'
' copy_Cell_A4 Macro
'
    Dim RowLocation As Long 'can hold over 32000 if over this many rows
'
    Range("A6").Select
    Selection.End(xlDown).Select
    RowLocation = ActiveCell.Row
    Range("A4").Select
    Selection.Copy
    Range("J6:J" & RowLocation).Select
    ActiveSheet.Paste
    
    Range("A6").Select
    Selection.End(xlDown).Select
    RowLocation = ActiveCell.Row
    Range("A2").Select
    Selection.Copy
    Range("K6:K" & RowLocation).Select
    ActiveSheet.Paste
    
    Range("A6").Select
    Selection.End(xlDown).Select
    RowLocation = ActiveCell.Row
    Range("E2").Select
    Selection.Copy
    Range("L6:L" & RowLocation).Select
    ActiveSheet.Paste
    
End Sub

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

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

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

Option Explicit
 
 
Sub copy_Cell_A4()
    Dim RowLocation As Long 'can hold over 32000 if over this many rows
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        RowLocation = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.Range("A4").Copy ws.Range("J6:J" & RowLocation)
        ws.Range("A2").Copy ws.Range("K6:K" & RowLocation)
        ws.Range("E2").Copy ws.Range("L6:L" & RowLocation)
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.Workbooks.Close SaveChanges:=True
End Sub

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

Option Explicit
 
 
Sub copy_Cell_A4()
    Dim RowLocation As Long 'can hold over 32000 if over this many rows
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        RowLocation = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.Range("A4").Copy ws.Range("J6:J" & RowLocation)
        ws.Range("A2").Copy ws.Range("K6:K" & RowLocation)
        ws.Range("E2").Copy ws.Range("L6:L" & RowLocation)
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.ThisWorkbook.Close SaveChanges:=True
End Sub