internal links b/w sheets of merged workbooks, using VBA (EXCEL MACROS)

Question

My aim is to merge all workbooks having multiple sheets from any specified folder to one workbook of multiple sheets. The problem is I don’t want external links to be maintained, If I use "breaklink" it will break all links(external links) b/w sheets of all workbooks. what I exactly I need is, After merging all sheets of workbooks in one workbook, I need links b/w these merged sheets.

CODE FOR MERGE ALL WORKBOOKS INTO ONE WORKBOOK :

Sub merge()

Dim FolderPath As String

Dim Filename As String

Dim Sheet As Worksheet

Application.ScreenUpdating = False

FolderPath = "C:\Users\Samiya jabbar\Desktop\test"

Filename = Dir(FolderPath)

Do While Filename <> ""

Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True

For Each Sheet In ActiveWorkbook.Sheets

Sheet.Copy After:=ThisWorkbook.Sheets(1)

Next Sheet

Workbooks(Filename).Close

Filename = Dir()

Loop

Application.ScreenUpdating = True

End Sub
```

Answer 1

Here are a couple of pointers:

• FolderPath should be a constant because it's value will never change
• Using a wildcard Filter with the path will ensure that you open the correct files
• Although ActiveWorkbook does the job, it is best to get in the habit of using qualified references
• Workbook.Worksheets returns a Worksheets Collection (not to be confused with a normal VBA Collection)
• Worksheets can be used to perform group operations on all of it's Worksheets at one time
• Download RubberDuck. Among its many great features is Code Indentation. It will save you a ton of time reformatting and show you unclosed code blocks

---

Sub merge()
    Const FolderPath As String = "C:\Users\Samiya jabbar\Desktop\test"
    Const Pattern As String = "*.xl*"
    
    Dim Filename As String

    Dim Sheet As Worksheet

    Application.ScreenUpdating = False

    Filename = Dir(FolderPath & Pattern)

    Do While Filename <> ""

        Workbooks.Open(Filename:=FolderPath & Filename, ReadOnly:=True).Worksheets.Move After:=ThisWorkbook.Sheets(1)

        Filename = Dir()

    Loop

    Application.ScreenUpdating = True

End Sub

Answer 2

First you have to open all files that are involved before you start moving. Now move sheets (don't copy them), instead of .copy, use .move . Save your merged workbook.

REVISED CODE FOR MERGE ALL WORKBOOKS INTO ONE WORKBOOK :

Sub merge()

Dim FolderPath As String

Dim Filename As String

Dim Sheet As Worksheet

Application.ScreenUpdating = False

FolderPath = "C:\Users\Samiya jabbar\Desktop\test"

Filename = Dir(FolderPath)

Do While Filename <> ""

Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True

For Each Sheet In ActiveWorkbook.Sheets

Sheet.move After:=ThisWorkbook.Sheets(1)

Next Sheet

Filename = Dir()

Loop

Application.ScreenUpdating = True

End Sub
```