VBA - Merge and bottons to click

[FadDak]

"Hello Excel experts,

I'm working on a project where I need to merge multiple sheets dynamically using VBA, and I'd like to create clickable buttons to streamline the process. Each sheet represents data for a specific month, and I want to merge them into a master sheet.

Could someone guide me on how to achieve the following:

1. Dynamic Sheet Merging:
   • How can I use VBA to dynamically merge sheets, considering that the number of sheets may vary, and new sheets might be added in the future?
2. Clickable Buttons:
   • What steps do I need to follow to create clickable buttons that users can press to trigger the sheet merging process?

I would appreciate any examples, code snippets, or step-by-step instructions to help me implement this functionality in my Excel project.

Thank you in advance for your assistance!

 

[eiloken]

i don't know this is what you need or not:

Sub MergeSheet()
    Dim ws As Worksheet, mSh As Worksheet
    Dim Criteria As String
    Dim splName() As String
    Dim i As Integer
    Dim lr As Long
    Set mSh = ThisWorkbook.Sheets("MERGE") 'assign a sheet named as MERGE to merge data in other sheets
    splName = Split(mSh.Range("C1").Value, "/") 'split each sheet name you entered in C1 that separate with /
    For Each ws In ThisWorkbook.Sheets 'loop through each sheet in workbook
        For i = LBound(splName) To UBound(splName)
            If LCase(Trim(ws.Name)) = LCase(Trim(splName(i))) Then 'check that sheet name that
                lr = mSh.UsedRange.Rows.Count
                ws.UsedRange.Copy Destination:=mSh.Range("A" & lr + 1) 'copy data from matched sheet and paste to merge sheet
            End If
        Next i
    Next ws
End Sub

you can insert a button or shape in sheet MERGE and assign it to the macro above like attach picture