-1

As mentioned above in the title. I have been looking out for ways. I have tried the method of using VB scripting to combine excel workbooks into one excel workbook. However, while i was doing the scripting, there was no sign of the compilation of all the workbook into the master workbook.

Here is my VB script that i've done: 

Sub GetSheets()
Path = "C:\Users\..."
Filename = Dir(Path & ".xlsx") 
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=This.Workbook.Sheets(1)
Next Sheet 
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
Community
  • 1
  • 1
XxMFxX
  • 3
  • 4
  • You may find this helpful : https://stackoverflow.com/a/30605765/4961700 – Solar Mike Jun 04 '18 at 06:25
  • Tried. It gives me a new excel workbook instead of compiling my files together. – XxMFxX Jun 04 '18 at 07:11
  • So, what is wrong with combining all the files into a new workbook - that does seem to be what you want... Edit the code to make it do what you need - that is what I did... – Solar Mike Jun 04 '18 at 07:24

1 Answers1

0

When you open a workbook, it becomes the active workbook.

Sub GetSheets()

    Path = "C:\Users\..."
    Filename = Dir(Path & "*.xlsx")
    Dim Wb As Workbook
    Dim Wb0 As Workbook: Set Wb0 = ActiveWorkbook
    Dim Sh As Worksheet

    Do While Filename <> ""
        Set Wb = Workbooks.Open(Path & Filename)

        For Each Sh In Wb.Sheets
            Sh.Copy After:=Wb0.Sheets(Wb0.Sheets.Count)
        Next Sh

        Wb.Saved = True
        Wb.Close
        Filename = Dir()
    Loop

End Sub
J.Doe
  • 596
  • 1
  • 3
  • 7
  • Hi Joe, i've also tried using your code, but there's still no sign of compilation of worksheets in the master workbook. – XxMFxX Jun 04 '18 at 03:35
  • Change `Filename = Dir(Path & ".xlsx")` to `Filename = Dir(Path & "*.xlsx")` and make sure your path ends with "\" – J.Doe Jun 04 '18 at 03:49