Geek Answers Handbook

Updating macros in a new combined workbook from several Excel workbooks

I combined the data of the book according to the code below:

Sub Merge2MultiSheets()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim MyPath As String
    Dim strFilename As String
    Dim CurrentRow As Integer
    Dim CurrentColumn As Integer
    Dim Index As Integer

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "D:\Excels" ' change to suit

    CurrentRow = 1
    CurrentColumn = 1
    Index = 0
    Set wbSource = ActiveWorkbook
    strFilename = wbSource.Worksheets("Test").Cells(CurrentRow, Index + 1)

    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    'strFilename = Dir(MyPath & "\*.xlsx", vbNormal)

    If Len(strFilename) = 0 Then Exit Sub

    Do Until strFilename = ""

       Set wbSrc = Workbooks.Open(fileName:=MyPath & "\" & strFilename)

       Set wsSrc = wbSrc.Worksheets(1)

       wsSrc.Copy after:=wbDst.Worksheets(wbDst.Worksheets.Count)

       Set wsSrc = wbSrc.Worksheets(2)

       wsSrc.Copy after:=wbDst.Worksheets(wbDst.Worksheets.Count)

       wbSrc.Close False

       Index = Index + 1
       strFilename = wbSource.Worksheets("Test").Cells(CurrentRow, Index + 1)

    Loop
    wbDst.Worksheets(1).Delete

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Now the problem is that each individual workbook has a button with a macro and some code when the button is pressed, which works fine in a separate file, but in the combined file, when I click the button, then it opens this source file to run the code on click button, it does not use code that is copied to the combined file.

Let me know how I can make sure that the merged file is independent of the source file to run the button click code and should be independent.

Individual button click code specified in the comment:

Sub Calculate_Click()
    Dim sum As Integer
    Dim mult As Integer

    Dim input1 As Integer
    Dim input2 As Integer

    input1 = Worksheets("test1_input").Cells(1, 2)
    input2 = Worksheets("test1_input").Cells(2, 2)


    sum = input1 + input2
    mult = input1 * input2

    Worksheets("test1_output").Cells(1, 2) = sum
    Worksheets("test1_output").Cells(2, 2) = mult

End Sub
+4
2

, , -, . ActiveX

, , "", , , , ActiveX. ActiveX.

+1

, , , :

Call Thisworkbook.MacroName

, . .

Thisworkbook.Sheetname

, .

0

More articles:


All Articles