Free Microsoft Excel 2013 Quick Reference

Combining Multiple Excel Workbooks Into One

Is there a way in VBA to select a folder with ~20 different Excel Workbooks. Have a loop perform the same task to each workbook in the folder and combine them into one excel workbook?

As of right now I have this code:


Sub MastercurveCreater()

Workbooks.Add

ActiveWorkbook.SaveAs "N:****** MasterCurve Start" & "InitialSave" & ".xlsx"

SelectData.Show

'
' DataSelection Macro
'
Range("K1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=-30
Range("K1:K68,N1").Select
Range("N1").Activate
Range(Selection, Selection.End(xlDown)).Select
Range("K1:K68,N1:P68").Select
Range("N1").Activate

Selection.Copy

Windows("InitialSave.xlsx").Activate

Range("A1").Select

ActiveSheet.Paste

SaveWorkbook.Show

'You can use this to delete all xlsx files in the folder Test
On Error Resume Next
Kill "N:MATDEVMasterCurve Start" & "InitialSave" & ".xlsx"
On Error GoTo 0

End Sub


It takes all the data from one spreadsheet to which I specify and copies and paste it into another blank worksheet.

I would like to do this for all the worksheets in the folder, but am unsure of how to go about doing it.

Any help would be appreciated.

Thanks a lot!


Hi,

Could someone give me the code to combine multiple excel sheets into one. It doesn't mattter how many rows are in between, just as long as nothing over laps.

Thanks.

I am trying to combine 12 similar worksheets into one master file. I used a sample code i found and made minimal adjustments. The issue I am having is that the macro runs, but it only shows the information for the last file selected(as i am selecting all 12 at the same time). I am new to coding, so my knowledge is very limited. I am sure that there is an easy fix, but I cannot figure it out.

Private Declare Function SetCurrentDirectoryA Lib _
    "kernel32" (ByVal lpPathName As String) As Long
    
Sub ChDirNet(szPath As String)
    SetCurrentDirectoryA szPath
End Sub


Sub Combine_Workbooks_Select_Files()
    Dim MyPath As String
    Dim SourceRcount As Long, Fnum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long
    Dim SaveDriveDir As String
    Dim FName As Variant
    
    With Application
      CalcMode = .Calculation
      .Calculation = xlCalculationManual
      .ScreenUpdating = False
      .EnableEvents = False
    End With
    
    SaveDriveDir = CurDir
    ChDirNet "E:desktopNRF-testMonthly Reports"
    
    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)
    
    If IsArray(FName) Then
       Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
       rnum = 1
       For Fnum = LBound(FName) To UBound(FName)
          Set mybook = Nothing
          On Error Resume Next
          Set mybook = Workbooks.Open(FName(Fnum))
          On Error GoTo 0
          If Not mybook Is Nothing Then
             On Error Resume Next
             With mybook.Worksheets(1)
                Set sourceRange = .Range("C1:L200")
             End With
             If Err.Number > 0 Then
                Err.Clear
                Set sourceRange = Nothing
             Else
        If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                    Set sourceRange = Nothing
                  End If
                End If
                On Error GoTo 0
                
                If Not sourceRange Is Nothing Then
                
                   SourceRcount = sourceRange.Rows.Count
                   
                   If rnum + SourceRcount >= BaseWks.Rows.Count Then
                      MsgBox "Not enough rows in the sheet."
                      BaseWks.Columns.AutoFit
                      mybook.Close savechanges:=False
                      GoTo ExitTheSub
                   Else
                      Set destrange = BaseWks.Range("A" & rnum)
                      With sourceRange
                         Set destrange = destrange.Resize(.Rows.Count, .Columns.Count)
                      End With
                      destrange.Value = sourceRange.Value
                      
                      rnum = rnum + SourceRcount
                    End If
                  End If
                  mybook.Close savechanges:=False
                End If
              Next Fnum
              BaseWks.Columns.AutoFit
            End If
ExitTheSub:
         With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = CalcMode
         End With
         ChDirNet SaveDriveDir
End Sub

I know that there should be tabs with the code, but it didnt paste right. Any help would be appreciated. Layman terms would also be appreciated. Thanks.

hi all,

Could someone help me to combine multiple text files into one file using excel VB code.

eg: I have three files below in C:temp

test.txt contains:
today is beautiful
but tomorrow will not be

test1.txt contains:
last year was good
but this year is better

test2.txt contains:
this week is the first week of the month
next week is the second week of the month

combined file contains:
today is beautiful
but tomorrow will not be
last year was good
but this year is better
this week is the first week of the month
next week is the second week of the month

thanks in advance for your help.

cheers,
bogia

Does anyone know how to view multiple excel workbook within one excel screen? i know there is a way to compare two workbook side by side(hortizontal or vertical). but is there anyway i can view all my workbook in one screen? Thanks..

Hi All,
I have a folder where we get 10-15 workbooks and we need to convert these workbooks into one. Currently we manually copy from these individual workbooks and then paste it to the one which we want to have all the data from all the workbooks. This is really time consuming. Can anyone of you help me with creating a Macro which will do the job ?

Appreciate all your help.

Thanks,
naveen

I need to be able to combine multiple csv files into one xlsx file. The csv files have 6 standard columns but I need to add a column when combining to represent the file name when combining because I need the filename has unique information for each file.

I have found a macro to combine the csv files into one xlsx file but I do not know how to append the filename of each csv into a column.

Csv sample
a,b,c,d,e,f
11/2/08,08:08,c:temp,AVD123,AVDDC,6.7
11/4/08,12:15,c:temp,AVD123,AVDDC,8.1

john.smith csv file

Csv sample
a,b,c,d,e,f
6/2/08,08:08,c:temp,AVD123,AVDDC,6.7
6/4/08,12:15,c:temp,AVD123,AVDDC,8.1

bob.jones csv file

After combination
Column A Column B Column C Column D Column E Column F Column G
11/2/08 08:08 c:temp AVD123 AVDDC 6.7 john.smith
11/4/08 12:15 c:temp AVD123 AVDDC 8.1 john.smith
6/2/08 08:08 c:temp AVD123 AVDDC 6.7 bob.jones
6/4/08 12:15 c:temp AVD123 AVDDC 8.1 bob.jones

I have 60 differant Excel files that are formatted the same way but need to
be combined into one list. I am curious if there is an easier way then
dragging in each worksheet and then copy and paste. Hoping for a quick
solution

I am using Office 2003 on Windows XP.

Take one Excel file that has been converted (saved as) an HTML document from
one user. Take another one from another user, and yet a third one.

I need to combine these into one single HTML document programmatically from
VBA. Is this possible? I've been looking at previous posts and I don't see
anything like this...

For the most part the files just contain text and numbers or formulas, but
rarely could include a chart or other graphic.

If it is possible, could some one please post example code?

If it is NOT possible, could someone please enlighten me?
Thanks much in advance.

Hi, Is there a way to combine about 4 workbooks into one workbook but to keep each workbook as a separate worksheet ?
Obviously there is 2 much data to do this manually with any efficiency.

Regards
Kevin

Hello everyone. I am a beginner of macro and VBA. I need to copy datas from mutiple CSV workbooks into one workbook. These csv files are in the same folder, and they got exatcly the same fomat and header. Is it possible to only copy the first file with header and just the date for the rest, then create a new file in another directory. Need help on this as it gonna save lots of time at work. Thanks in advance for your help.

Hi,

I am using MS excel 2003,I have an excel file for the each day named"summary_01 and summary_02 and so on...up to summary_31.

Each excel file contains three rows 1st headings ,2nd row contains GUJ details and 3rd RAM details.

Now I want to make summary of All GJ and All RM details showing day wise details.

How can I combine these excel sheets into one.??

Gaurav

hi,
i tried to find a macro to consolidate multiple excel files into one file, in the forum but could not find anything which suits my requirements....
can someone pls help me with this....

i have approximately 30 excel files in a folder, which i should consolidate into one sheet...
the path of the folder is not same...it changes from day to day...
for instance the files which we use today will be saved in today's dated folder i.e., "Day shift 12/19/2010"...
i should consolidated all the files in the above folder at the end of the day...which i should repeat daily...
the excel file contains data only in sheet1...
and rows and columns are not fixed...
each files contains header... and the header should include only ones in master sheet.

your help is greatly appreciated...

I have five EXCEL 2003 workbooks containing columns of names, addresses,
phone numbers, and other columns. Each workbook is formatted the same. I
want to combine these five files into one workbook that includes all of the
information in the five separate files. I searched for EXCEL help on-line
(“combine workbooks”) but the solutions seem complicated.

What is the easiest way to combine these five EXCEL files into one big file
that includes all of their data? I will appreciate advice.

I am trying to combine ~300 workbooks into one single workbook. All 300 workbooks have the exact same header. I tried using the code from thread http://www.excelforum.com/showthread.php?p=696435 but nothing is being copied over. The only difference between my example and the other is I only need to take data from the first sheet in each data workbook. All the workbooks are located in following directory
The “master file” is located in another directory.  The “master file” also has the same header as the data workbooks. 
Basically, I want to retrieve all data (excluding the header) from the first data workbook and copy to the master file.  Then
I want to go to the second workbook and retrieve all data from the second data workbook and copy to master file, and so on. 
The code I am using to combine is as follows: 
	

	
 Sub Get_Value_From_All()
    Dim wbSource As Workbook
    Dim wbThis As Workbook
    Dim rToCopy As Range
    Dim uRng   As Range
    Dim rNextCl As Range
    Dim lCount As Long
    Dim bHeaders As Boolean
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow   As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False

        On Error Resume Next

        Set wbThis = ThisWorkbook
        'clear the range except  headers
        Set uRng = wbThis.Worksheets(1).UsedRange
        If uRng.Cells.Count <= 1 Then
            'no data in master sheet
            bHeaders = False
            GoTo search
        End If
        uRng.Offset(1, 0).Resize(uRng.Rows.Count - 1, _
                                 uRng.Columns.Count).Clear
search:
        With .FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "F:Excel TipsCombine WorkbooksWorkbookData"
            .FileType = msoFileTypeExcelWorkbooks

            If .Execute > 0 Then    'Workbooks in folder
                For lCount = 1 To .FoundFiles.Count    ' Loop through all.
                    'Open Workbook x and Set a Workbook  variable to it
                    Set wbSource = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                    For i = 1 To Sheets.Count - 1
                        Set rToCopy = wbSource.Worksheets(i).UsedRange
                        Set rNextCl = wbThis.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                        If bHeaders Then
                            'headers exist so don't copy
                            rToCopy.Offset(1, 0).Resize(rToCopy.Rows.Count - 1, _
                                                        rToCopy.Columns.Count).Copy rNextCl
                            'no headers so copy
                            'place headers in Row 2
                        Else: rToCopy.Copy Cells(1, 1)
                            bHeaders = True
                        End If
                    Next i
                    wbSource.Close False     'close source workbook
                Next lCount
            Else: MsgBox "No workbooks found"
            End If
        End With

        CalcMode = .Calculation
        .Calculation = xlCalculationManual
    End With
'not checked following code
            With ActiveSheet
                .Select
    
                ViewMode = ActiveWindow.View
                ActiveWindow.View = xlNormalView
    
                .DisplayPageBreaks = False
    
                Firstrow = .UsedRange.Cells(1).Row
                Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
                For Lrow = Lastrow To Firstrow Step -1
                    With .Cells(Lrow, "A")
                        If Not IsError(.Value) Then
                            If .Value = "" Then .EntireRow.Delete
                        End If
                    End With
                Next Lrow
            End With
            On Error GoTo 0
            ScreenUpdating = True
            DisplayAlerts = True
            EnableEvents = True
        'End With
End Sub
Thank you in advance for any assistance.

Hi,
I'm looking for some Excel VBA code which will allow me to copy data from multiple workbooks (just the one worksheet is contained in each workbook) into a single spreadsheet (one worksheet). The number of columns will remain fixed. However, the number of rows with data in each workbook will be variable. I would want the data to be copied immediately underneath each other with no empty rows in the single worksheet.

Any help on this would be greatly appreciated.

Thanks.

I have tried serveral versions of code, provided in these forums, but have yet to find one that works successfully for me. I'm looking to combine numerous excel worksheets, all in separate files (.xls and .xlsx), into one master file on a single worksheet. All of the files to be combined have two tabs (Assessments and Certifications). I need to combine all of the data from the "Certifications" tab of each file, into the master sheet. The first three rows of each file, contain the exact same header information. Any assistance is GREATELY appreciated!

Thanks!

Hi there,

I'm currently still new to Excel VB scripting, but have a fair amount of C/C++ experience. That said, I'm trying to write a script that will move a large amount of information (~400 cells) from 400 worksheets in one workbook (input sheets 1-400) into 1 worksheet in another workbook (output). I recorded an example macro to get some foundation code for what I need to do, however I am not sure how to change the workbook and worksheet name in Formula string.
The example below is the macro code that was automatically generated when I moved some of the averaged data from the input.xls into cells in the output.xls book


	VB:
	
Windows(outputBook.Name).Activate 
 
ActiveCell.FormulaR1C1 = _ 
"=AVERAGE('[Input.xls]12-Jan-05 14h 15m 34s'!R25C2:R26C2)" 
Range("C2").Select 
ActiveCell.FormulaR1C1 = _ 
"=AVERAGE('[Input.xls]12-Jan-05 14h 15m 34s'!R25C3:R26C3)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now, I have an array of all of the sheetnames in the inputBook, and I've concatinated the inputBook name and sheetname so its in a similar format to the "[Input.xls]12-Jan-05 14h 15m 34s", by using the commands below.


	VB:
	
 
teststring = "[" & imputBook.Name & "]" & sheetname(inx) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So, what I want to do is change the formulas above using a variable within the formula, which I thought might look something like this:

	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=AVERAGE(teststring!R25C2:R26C2)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, the code segment immidiately above does not work.

So, does anyone know how to move from multiple worksheets in one input workbook into 1 worksheet in another output workbook? In particular, I was wondering how to change the formula in VB script to be dependent on a variable (teststring) above.

I know this is a complicated question, but if you think I'm going about this the wrong way or have any suggestions, please help me out. I'd greatly appreciate it!

Thanks,
David

I want to aggregate data from multiple workbooks into one. I have five
workbooks with identical columns and formats, and want to run a macro in a
separate workbook that copies all rows (starting with row#2 and ending at the
last row with data) from the five workbooks and pastes it all into the new
one. Or something like that. Thanks in advance.

Hi -- wondering if anyone can help me. I have a file with 725 fields
currently sitting in an Excel workbook across 3 worksheets. I need to
do some work on it there, then export the results back into one giant
tab-delimited file with 725 fields.

I've searched and seen plenty of posts about exporting multiple sheets
where the sheets need to follow one another vertically (EG, additional
records) in the text file, but mine need to follow horizontally (EG,
additional fields).

I don't know if I'm being clear, but hope someone can help.

Thanks,
Char

Hello,

I'm trying to merge several sheets (not all) from multiple workbooks into one sheet in a single workbook.

The data I need to copy is placed in sheets Client 1, Client 2, Client 3 in the example file I've attached; I also need to copy cell C3 from summary sheet and paste it at the 1st right sided cell available, along the same range (it will allow me to group the clients from each workbook) .

The example has 3 client sheets, but our workbook has up to 16. The Client sheets always go from sheet 3 to 18, but some users delete the client sheets that have no data, so the amount of sheets to be copied may vary.

Is it possible to skip sheets, using some kind os argument like "if A1=0, go to next sheet"?

Thanks in advance,
Alexandra

Is there any way in excel to combine two rows of data into one and have it also sum the combined data

I get a dump of about 70 text files into a shared folder each week...lets call the folder "Data Dump". All of the files in the folder are named with a txt extension such as 5474.txt, tudfhd.txt ,or dysh.txt...etc. I need a way to combine all of these files into one and get it into Excel so I can start creating a Macro for it. Anyone know how to do that....Thanks in advance.

I have 20 excel files and each excel file contains 3 worksheets. How can I
combine these files into one by only a few clicks?

In the past, I did it by right click 'move or copy' and one by one.

I have five workbooks, each containing names, addresses, phone numbers, and a
couple of other columns. Each workbook is formatted the same. I want to
combine these five workbooks into one workbook. I searched for "combine
workbooks" in the Excel on-line help but the suggestions seem complicated.

What is the simplest way to combine these five workbooks into one workbook?
I will appreciate advice.