Import Multiple Excel Work Sheets into Master excel file using VBA

[hanan_ak]

Hi,

I am new to this forum. I am in need of help in importing excel files into master file automatically through VBA. Below is the scenario.

1) Currently, I have 23 stores P&L statements, which I export from Sage software every month, then I open them one by one and select the whole sheet and unmerge them.

2) I have master file. Once, I have unmerged all, I copy all those individual files into excel master file manually.

What I am looking to get a VBA code, through which, when I press the Import button in Master file. It open file browse window. From there when I select all the files from a particular location. It should copy all the individual sheets into the master file in separate Tabs and rename the tabs name as per the individual file name and then unmerge all the copied individual sheets.

I have searched the internet a lot and couldn't find any solution (VBA code).

I have shared the files for your reference (the individual file Before and after merging).

https://drive.google.com/open?id=1639u-iQk5tHc1viWPWXR95DjknF3Czli

I shall be very thankful in advance.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...-sheets-into-master-excel-file-using-vba.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

Hi & welcome to the board.
How about

Sub GetFilesUnMerge()

   Dim FNames As Variant
   Dim Cnt As Long
   Dim Wbk As Workbook
   Dim MstWbk As Workbook
   Dim Ws As Worksheet
   
   Set MstWbk = ThisWorkbook
      
   FNames = Application.GetOpenFilename(FileFilter:="Excel files (*.xls*), *.xls*", MultiSelect:=True)
   If Not IsArray(FNames) Then Exit Sub
   For Cnt = 1 To UBound(FNames)
      Set Wbk = Workbooks.Open(FNames(Cnt))
      For Each Ws In Wbk.Worksheets
         Ws.Copy before:=MstWbk.Sheets(1)
         MstWbk.Sheets(1).Cells.UnMerge
      Next Ws
      Wbk.Close False
   Next Cnt
End Sub

 

[hanan_ak]

Hi,

O my God!!! That is amazing. I cant believe my eyes, the code works perfectly as per my requirement. Thanks a lot for your help. But when I was testing it again, there is one slight complication I came across. If the individual files tabs name are similar, it breaks the importing process (Error: The name is already taken).

When importing individual files, I would like the imported files to rename their tabs itself in the master file as per the individual workbooks name. Suppose I am importing multiple individuals files (named : dav, keni, bri etc). When I run the code, in the master file the imported file tab should rename it to "dav", "keni", "bri", etc.

"There is no exercise better for the heart than reaching down and lifting people up".

If you could help me in this regard, I am already really grateful for your help and will really appreciate you could help me out again.

 

[Fluff]

Does each file consist of 1 sheet?

 

[hanan_ak]

Yes, each file consist only 1 sheet and the default sheet name is "Departmental Profit & Loss" for all sheets.

What I export the individual files from Sage, I save them as per department name like, if am downloading Dav P&L, then I will save it as dav. All I want is, when importing this dav P&L into Master file, the tab name should be as per the imported file which is "dav".

Thanks.

 

[Fluff]

Ok, try this

Sub GetFilesUnMerge()

   Dim FNames As Variant
   Dim Cnt As Long
   Dim Wbk As Workbook
   Dim MstWbk As Workbook
   Dim Ws As Worksheet
   
Application.ScreenUpdating = False
   Set MstWbk = ThisWorkbook

   FNames = Application.GetOpenFilename(FileFilter:="Excel files (*.xls*), *.xls*", MultiSelect:=True)
   If Not IsArray(FNames) Then Exit Sub
   For Cnt = 1 To UBound(FNames)
      Set Wbk = Workbooks.Open(FNames(Cnt))
      Wbk.Sheets(1).Copy before:=MstWbk.Sheets(1)
      MstWbk.Sheets(1).Name = Left(Wbk.Name, InStr(1, Wbk.Name, ".") - 1)
      MstWbk.Sheets(1).Cells.UnMerge
      Wbk.Close False
   Next Cnt
End Sub

 

[hanan_ak]

This code works exactly the way I wanted. I have no words to describe my feelings and how Thankful I am to you.

You Rocks!!!

 

[Fluff]

Glad to help & thanks for the feedback