CODEWITHSUNDEEP
vbaexcel
Ravi Sahu
Ravi Sahu

Reputation: 43

Macro link update in new combined workbook from multiple excel workbook

I have merged the workbook data as per the below code:

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 macro and some code on button click which work fine in individual file but in merged file when I click on the button then it opens that original file to run the code on button click, it is not using the code which is copied in merged file.

Let me know how can I make sure that the merged file doesn't depend on original file for running the button click code and should be independent.

Individual button click code as asked in 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

Upvotes: 4

Views: 227

Answers (2)

manpreet
manpreet

Reputation: 675

I tested the code and problem seems to be limited to Form Buttons only. Try Using ActiveX buttons

In The Developer Tab, In Controls Group, On clicking the Insert dropdown button, You will notice that there are 2 groupings, upper one with heading Form Controls and lower one having heading ActiveX Controls. Insert buttons from the ActiveX Controls.

Upvotes: 1

Dobbes
Dobbes

Reputation: 134

If you want to run the macro specific to the merged file you are executing from, preface:

Call Thisworkbook.MacroName

Be sure you initialize to the proper page in the workbook before executing. You can also control that with Thisworkbook.

Thisworkbook.Sheetname

That will keep your execution from running in the original books.

Upvotes: 0

Related Questions