Copy Multiple excel file to one master

Topic

New Reply

Hi All,

I need a code that go in specific folder then copy the multiple files wroksheets to master file?

Thanks and Regards,
farrukh

Reply | Quote

Replies

You were not very specific. Can you modify this? It takes all the workbooks in “C:MyPath” (change as desired) and adds the first worksheet to the end of the active sheet

Steve

Code:

Option Explicit
Sub CombineMultipleFiles()
' Path - modify as needed but keep trailing backslash
  Const sPath = "C:MyPath"
  Dim sFile As String
  Dim wbkSource As Workbook
  Dim wSource As Worksheet
  Dim wTarget As Worksheet
  Dim lRows As Long
  Dim lMaxSourceRow As Long
  Dim lMaxTargetRow As Long
  'Dim blnNoHeader As Boolean
  
  On Error GoTo ErrHandler
  Application.ScreenUpdating = False
  
  Set wTarget = ActiveSheet
  lRows = wTarget.Rows.Count
  sFile = Dir(sPath & "*.xls*")
  Do While Not sFile = ""
    Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
    Set wSource = wbkSource.Worksheets(1)
    lMaxSourceRow = wSource.Cells(lRows, 1).End(xlUp).Row
    lMaxTargetRow = wTarget.Cells(lRows, 1).End(xlUp).Row
    wSource.Range("1:" & lMaxSourceRow).Copy _
      Destination:=wTarget.Cells(lMaxTargetRow + 1, 1)
    wbkSource.Close SaveChanges:=False
    sFile = Dir
  Loop
  
ExitHandler:
  Application.ScreenUpdating = True
  Exit Sub
  
ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

Reply | Quote

HI sdckapr,

I am getting error application defined or object defined error ?

Thanks and Regards,
Farrukh

Reply | Quote

Which line gives the error and under what circumstances?

Steve

Reply | Quote

Sorry i it works fine that was my fault, Steve i am testing the code put two excel work book in the folder path C:MyPath. The code opens both excel file but copy only one worksheet from one excel file i have 30,30 excel worksheet in workbooks ,but just getting the one excel sheet into the master any help please…

Thanks and Regards,
farrukh

Reply | Quote

Change the line
Set wSource = wbkSource.Worksheets(1)
to:
For Each wSource In wbkSource.Worksheets

And add the Line “Next” like below:

wSource.Range(“1:” & lMaxSourceRow).Copy _
Destination:=wTarget.Cells(lMaxTargetRow + 1, 1)
Next
wbkSource.Close SaveChanges:=False

This will loop through all the worksheets in each of the workbooks that it opens instead of just using the first worksheet.

Steve

Reply | Quote

Dear Steve,

I’m a newbie for this forum. I’m looking for this kind of solution for my excel files and I found this post. Very useful.

However I do need an extra function if is possible. I need that based on a condition in a specific column of each workbooks (common in the files that I have) the macro runs and give me the rows that I pretend.

Example: I do have 12 files with the overtime of the employees during Jan to Dec (Column A – Name, Column B – Date, C – Nº Hours). I want to compile the information by selecting when Column A = John. The new file will give me the resume of John/hours during that year in a single file).

Thanks for your help,
Drsker

Reply | Quote

Hi steve i have change the lines but still it getting only one sheet to master?

Code:

Sub CombineMultipleFiles()
' Path - modify as needed but keep trailing backslash
  Const sPath = "D:path"
  Dim sFile As String
  Dim wbkSource As Workbook
  Dim wSource As Worksheet
  Dim wTarget As Worksheet
  Dim lRows As Long
  Dim lMaxSourceRow As Long
  Dim lMaxTargetRow As Long
  'Dim blnNoHeader As Boolean
  
  On Error GoTo ErrHandler
  Application.ScreenUpdating = False
  
  Set wTarget = ActiveSheet
  lRows = wTarget.Rows.Count
  sFile = Dir(sPath & "*.xls*")
  Do While Not sFile = ""
    Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
    For Each wSource In wbkSource.Worksheets
    lMaxSourceRow = wSource.Cells(lRows, 1).End(xlUp).Row
    lMaxTargetRow = wTarget.Cells(lRows, 1).End(xlUp).Row
    wSource.Range("1:" & lMaxSourceRow).Copy _
      Destination:=wTarget.Cells(lMaxTargetRow + 1, 1)
      Next
    wbkSource.Close SaveChanges:=False
    sFile = Dir
  Loop
  
exithandler:
  Application.ScreenUpdating = True
  Exit Sub
  
ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume exithandler
End Sub 

Thanks and Regards,
farrukh

Reply | Quote

Could you detail what you are testing with and what the results are? Also what you want the results to be.

It works in my testing. All the worksheets in all the workbooks end up in the activesheet.

Steve

Reply | Quote

Hi Steve sorry i am not making you understand. Suppose i have 10 workbook in a folder and each workbook has few worksheets. I want each workbook (worksheet) copy to master file (with different tabs).

Thanks for helping me
farrukh

Reply | Quote

Is this what you are after?
Steve

Code:

Option Explicit
Sub CopyWorksheets()
' Path - modify as needed but keep trailing backslash
  Const sPath = "C:MyPath"
  Dim sFile As String
  Dim wbkSource As Workbook
  Dim wSource As Worksheet
  Dim wbkTarget As Workbook
  
  On Error GoTo ErrHandler
  Application.ScreenUpdating = False
  
  Set wbkTarget = ActiveWorkbook
  sFile = Dir(sPath & "*.xls*")
  Do While Not sFile = ""
    Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
    For Each wSource In wbkSource.Worksheets
      With wbkTarget
        wSource.Copy After:=.Sheets(.Sheets.Count)
      End With
    Next
    wbkSource.Close SaveChanges:=False
    sFile = Dir
  Loop
  
ExitHandler:
  Application.ScreenUpdating = True
  Exit Sub
  
ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

Reply | Quote

Hi Steve
This is nearly what I am wanting. I need the formula to only import select(I have a list on the open workbook) files, not all the saved saved in the location. Can you please help me?

Reply | Quote

Dear Steve,

That is too wonder full this is my need you have done it,Thank alot 🙂

Farrukh

Reply | Quote

Hi Steve,

Do you have a code that go in specific folder let say C:/Path/List/

1.) On that folder there are multiple List excel files (List 1.xls, List 2.xls etc) having 3 sheets (TabName1, TabName2, TabName3) on each List#.xls

2.) Is there a way to only copy a specific tab name (eg just TabName2) in a Master file? TabName2 is unique per excel file

3.) Can I also have a macro that Master file will populate all the excel in C:/Path/List/, if it is exsiting in the master file it will not generate another copy of the TabName2

Thanks in advance

Reply | Quote

You should be able to modify the code attached yourself…
1) change the line of the code with the new path. The path is explicitly written into the code
2) Don’t loop through all the worksheets, just copy the desired one
3) Not sure I understand, but it seems that you want to check the existence of a worksheet into the file and only copy if it doesn’t exist. add that as an IF to the code to check if the worksheet name is already into the target worksheet.

[Item 3 is a little confusing. Once you open up the first workbook and copy the TabName2 from it into the master, what would be the point of opening up any other workbooks since at this stage you know it exists in the master since you just added it?]
Steve

Reply | Quote

Hi,

Thanks for your response Steve, I have no backgrounnd in VBA, can i ask you to provide the correct code please. Sorry!

1) change the line of the code with the new path. The path is explicitly written into the code
done with this…
2) Don’t loop through all the worksheets, just copy the desired one
I need to loop all the files from the source folder to make sure that whatever changes, the master will get it.

3) Not sure I understand, but it seems that you want to check the existence of a worksheet into the file and only copy if it doesn’t exist. add that as an IF to the code to check if the worksheet name is already into the target worksheet.
can I ask a sample If code for this. You’re correct I need to copy the document not yet existing or just to overwrite the existing document to the latest one.

Reply | Quote

My goal is to help teach you VBA if you are going to work with it, not do all your work for you.

You can loop through all the files, just don’t loop through all the worksheets in each file, if you only want to copy 1 particular worksheet. But you still haven’t explained exactly what you want done. If the first workbook opened has the worksheet of interest and it gets copied into the master, there seems to be no need to open any other ones as the worksheet now exists in the master, so no other ones would be copied…

This makes me conclude that I don’t understand what you want, so there is no need to write code. Perhaps you should detail the steps you want to do. Make it simple assume there are only a master and 3 other files in the folder to work with and excel is closed. Walk me through what you would do manually, when you run the code and what the code would do…

Steve

Reply | Quote

Hi Steve!
What about copying a specific cell (say E6) from multiple .csv files to just one worksheet?
I tried for hours and can’t figure it out…

Reply | Quote

Hi Steve,

I’m trying to apply the above example to paste the data in columns (not in rows) but the values are being pasted all in the same column. So I only can see the result from the last file copied.

I’m using:
———————
Option Explicit
Sub CombineMultipleFiles()
‘ Path – modify as needed but keep trailing backslash
Const sPath = “C:MyPath”

Dim sFile As String
Dim wbkSource As Workbook
Dim wSource As Worksheet
Dim wTarget As Worksheet
Dim lColumns As Long
Dim lMaxSourceColumn As Long
Dim lMaxTargetColumn As Long
‘Dim blnNoHeader As Boolean

On Error GoTo ErrHandler
Application.ScreenUpdating = False

Set wTarget = ActiveSheet
lColumns = wTarget.Columns.Count
sFile = Dir(sPath & “*.xlsx*”)
Do While Not sFile = “”
Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
Set wSource = wbkSource.Worksheets(“1”)
lMaxSourceColumn = wSource.Cells(lColumns, 1).End(xlUp).Column
lMaxTargetColumn = wTarget.Cells(lColumns, 1).End(xlUp).Column
wSource.Range(“B5:B8”).Copy _
Destination:=wTarget.Cells(lMaxTargetColumn + 1, 2) ‘to start column B
wbkSource.Close SaveChanges:=False
sFile = Dir
Loop

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
———————

What I’m doing wrong?

Many thanks

LL

Reply | Quote

Are you perhaps looking for this:

Code:

lMaxSourceColumn = wSource.Cells(1, lColumns).End(xlToLeft).Column
lMaxTargetColumn = wTarget.Cells(1, lColumns).End(xlToLeft).Column

Steve

Reply | Quote

It’s working fine!! 😀

Many thanks Steve

Reply | Quote

Steve, I’m trying to only copy over the data from a table…Without the headers and the Macro buttons i’ve created is there a way to do that?

this code is great, i really appreciate the help

carney

Reply | Quote

I would recommend NOT putting them into 12 separate files. I would add a column with month to all the data and them combine them into 1 file. Then you can use autofilter to just see a particular persons data. You could use a pivot table (for example) to list by person (rows) and months(columns) the sum of the overtime.

Steve

Reply | Quote

Have you tried adapting the generic code in this thread? You don’t provide specifics so I can only provide general responses…

Steve

Reply | Quote

Have you tried adapting the generic code in this thread? You don’t provide specifics so I can only provide general responses…

Steve

Is this what you are after?
Steve

Code:
Option Explicit
Sub CopyWorksheets()
‘ Path – modify as needed but keep trailing backslash
Const sPath = “C:MyPath”
Dim sFile As String
Dim wbkSource As Workbook
Dim wSource As Worksheet
Dim wbkTarget As Workbook

On Error GoTo ErrHandler
Application.ScreenUpdating = False

Set wbkTarget = ActiveWorkbook
sFile = Dir(sPath & “*.xls*”)
Do While Not sFile = “”
Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
For Each wSource In wbkSource.Worksheets
With wbkTarget
wSource.Copy After:=.Sheets(.Sheets.Count)
End With
Next
wbkSource.Close SaveChanges:=False
sFile = Dir
Loop

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

I have used this code, but it brings all files saved in location. I want it to bring only the files listed in column A from my open workbook.

Reply | Quote

Can you attach a sample workbook and explain exactly what you want to do?

in a general sense, instead of looping with the DIR to look for folders on the drive, you would loop through the range of cells with the workbook name. The rest of the code would be the same, it is just a modification of the files you want to open.

Again, If you want specific answers you need to provide specific questions…

Steve

Reply | Quote

Hi Steve
I have attached a file, its also has some screen shots. Thanks so much for your help

Reply | Quote

As I outlined earlier…

Code:

Option Explicit
Sub CopyWorksheets()
' Path - modify as needed but keep trailing backslash
  Const sPath = "C:Test"
  Dim sFile As String
  Dim wbkSource As Workbook
  Dim wSource As Worksheet
  Dim wbkTarget As Workbook
  Dim wFiles As Worksheet
  Dim x As Long
  On Error GoTo ErrHandler
  Application.ScreenUpdating = False
  
  Set wbkTarget = ActiveWorkbook
  Set wFiles = ActiveSheet
  x = 0
  sFile = wFiles.Range("A2").Value
  Do While Not sFile = ""
    Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
    For Each wSource In wbkSource.Worksheets
      With wbkTarget
        wSource.Copy After:=.Sheets(.Sheets.Count)
      End With
    Next
    wbkSource.Close SaveChanges:=False
    x = x + 1
    sFile = wFiles.Range("A2").Offset(x, 0).Value
  Loop
  
ExitHandler:
  Application.ScreenUpdating = True
  Exit Sub
  
ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

Reply | Quote

Thank you so much, it is perfect

Reply | Quote

You are very welcome.

Steve

Reply | Quote