Is it possible to modify a macro code using another macro in Excel VBA

Question

Actually I have written a same macro for multiple workbooks which does the same work, the growth of the workbook become too longer from start to now i.e. it's nearly 700+ as of now, so if I need to modify a code slightly in macro, I need to open and update in all the workbook manually. So is there any solution for updating the same code to all macro.

as follows,

abc1.xlsm - has macro-A running
abc2.xlsm - has macro-A running
abc3.xlsm - has macro-A running
abc4.xlsm - has macro-A running
abc5.xlsm - has macro-A running
...........
abc700.xlsm - has macro-A running

all the 700 files have the same macro running, if i update the macro code in one file say abc1.xlsm then that code should be updated in all the excel files. is there any solution for this??

You asked the wrong question ([What is the X/Y-Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem)). Make your code dynamical so you don't need to change anything (each time). [Edit] your question, and include your code. Then ask about your actual problem. — Pᴇʜ, Apr 25 '19 at 07:23
That sounds like a perfect task for an add-in: [How to Create and Use an Excel Add-in](https://trumpexcel.com/excel-add-in/) — Pᴇʜ, Apr 25 '19 at 12:13

Error 1004 · Answer 1 · 2019-04-25T09:02:40.637

Option Explicit

Sub test()

    Dim LastRow As Long

    With ThisWorkbook.Worksheets("Sheet1") 'Here you refer to the specific workbook & worksheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Avoid using fix number for last row. Calculate last row using this method.
    End With

End Sub

Notes:

The with statement refer to the current workbook, sheet 1
Calculate the last row of column A, in Sheet1 & Current Workbook.

Another Way - Call Function

Option Explicit

Function Get_LastRow(ByVal ws As Worksheet, ColumnNo As Long)

    With ws
        Get_LastRow = .Cells(.Rows.Count, ColumnNo).End(xlUp).Row 'Avoid using fix number for last row. Calculate last row using this method.
    End With

End Function

Sub test()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim ColumnNo As Long, LastRow As Long

    Set wb = ThisWorkbook 'Set your workbook.
    Set wb = Workbooks("Book1") 'Another way to set workbook

    Set ws = wb.Worksheets("Sheet1") 'Set your worksheet

    ColumnNo = 1 'Set the column from where you want the last row

    LastRow = Get_LastRow(ws, ColumnNo)  'Call the function to count last row of column 1 in worksheet 1 in workbook Book1

    MsgBox LastRow

End Sub

Note:

Set your workbook, worksheet & ColumnNo and run the code. You will receive a message box indicating last row.

score 1 · Answer 2 · answered Apr 25 '19 at 07:50

1

Solution is to pass parameter to a macro and then have a Select...Case statement dependant on that parameter.

Inside this statement you'll have different pieces of code to execute.

answered Apr 25 '19 at 07:50

Michał Turczyn

32,028
14
47
69

Is it possible to modify a macro code using another macro in Excel VBA

2 Answers2