Free Microsoft Excel 2013 Quick Reference

Combining Multiple Excel Sheets into One Sheet

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.


Post your answer or comment

comments powered by Disqus
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,
pls help my. how to combine 2 sheets into one sheet. Example below :

sheet1:
A B C
1 1 1
2 2 2

sheet2 :
D E F
1 2 3
3 2 1

sheet3(result) :
A B C D E F
1 1 1 1 2 3
2 2 2 3 2 1

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

Hi,

I'm trying to write a macro. Currently it asks the user how many sheets the
user has e.g. 5 and opens the 5 sheets.
What i need to be able to do is copy the data from these sheets into one
sheet then output it into another workbook.
So if the first sheet has 6 rows, then those rows will start at (A1)
if the next one has 2 rows it should start at the 7th row

Hi
I want to copy rows depending on columm criteria (Columm J >0) from 10
different Sheets, into one Sheet. Hope that someone now how to do it.

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 need to combine multiple sheets into one sheet so I can sort them all at
one time. I have a workbook I received from another person. Instead of just
having one worksheet, it has many worksheets, each a continuation of the one
before it. If it were only a few sheets I could just copy each sheet and
paste it onto the bottom of the first sheet. Is there a way of combining all
of these sheets?

I need some help copying data from 50 excel files into one sheet without having to copy and paste. I receive around 200 excel attachments a day and they all have the same data and header rows. I just need to put them all on one sheet without having to manually copy and paste each row into a master sheet. Any help would be appreciated. thanks

Hello,

I have been struggling with this Macro for a while and I have tried in every way that's suggested on the web. This Macro works fine to copy data from multiple sheets into one master sheet, but it is also copying the formula. How can I change this Macro so that it is copying and pasting VALUE only?

Sub Combine()
Dim J As Integer

On Error Resume Next

' work through sheets
For J = 4 To Sheets.Count ' from sheet 3 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A10").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets("Combined").Range("A65536").End(xlUp)(2)

Next
End Sub
Please help. I need to resolve this quickly.

Thanks,

Yang

I am trying to merge sheets of changing size into one.

At work I am working with Filemaker Pro 6. I have a database that I use to keep track of addresses of mailing lists. Due to my lack of programming capability with Filemaker Pro I have been able to successfully export the differing queries into 8 different Excel Workbooks.

From here I have two options:

1) From those workbooks I know how to create a macro that will combine the sheets in the separate workbooks into one workbook with 8 sheets. From there I can create a macro to merge those sheets into one sheet, but when the sheets change in the future (getting longer or shorter), the macro wouldn't adjust capturing the information from the used cell in the sheets to merge to my master sheet.

2) I can do the same thing, as far as merging the 8 workbook sheets into a master sheet and bypass the merge to separate sheets in one workbook, but then I run into the same problem of not having my Macro or VBA adjust for sheets of changing size.

Is is possible to create code that will adjust for each sheet in capturing the information that will be changing?

Thank you.

I have 3 different excel sheets and data in each sheet starts from b5:q5. In a1 to b4 there is some other data. All i want is to copy the data from b5:q5 from all the three sheets and consolidate into one sheet. I have used the following macro but i am not getting the output properly. Can anyone please help me on this.

Thanks
sansri

Private Sub CommandButton1_Click()
    
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("b5:q5").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("a1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("b5").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
    
End Sub
Hi,

Hi all,

I have created a userform within Excel for people to fill in data for a project we're running. Every member has its own file and when it's closed the data in that file gets overwritten. The data is stored on the first sheet, starting from the 2nd row. The amount of data is different in each file as some fill in more than others.

I have created a masterfile with all the links of the project files on the first sheet. On the second sheet I wish to import all the data from the project files. The layout of these files are exactly the same. (only the number of rows with data vary as mentioned above)

Can anyone help me with some VBA code to get all that data into one sheet?

Thank you.

rgds, Marcel

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

Hi,
I have an Excel file which consists of several sheets. I can select all sheets but how do I merge the content into one sheet. The sheets only contain text so I don't have to summarize data.

Best regards,
Gro

Hi

I have a few different worksheets that i would like to merge into one sheet with a macro. All the worksheets has a colum with an ID number, the same ID number can be found in different worksheets. So basicly the different worksheets gives different information about an ID number.

I posted an example file where i want worksheet A and B to be merged into a new sheet and look like AB.

I don't know how to do this, if you could provide me with any code or advice it would be appreciated.

Thanks

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...

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!

I am having data which is entered by different person's say a,b,c,d & e in
different sheets (a,b,c,d & e). I want to combine all this data into one
sheet called Combine.
Whereas the columns are same in the five person's sheet say (x,y,z). Do you
have any formula for this.

Thanks & regards,

D.V.Sreenivas

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 all, I am wondering how I can use VBA to extract data from multiple excel files onto one summary workbook that pulls the data from the most recent date. All of the excel files have different names but each worksheet is labeled "Financials". Each worksheet is formatted almost identically - all of the metric names are in column C and the data starts in column D, but not all of the rows are identical. I would like the Summary workbook to be able to reference a formula in order to pull the correct data. A formula that works in each of the workbooks is "=INDEX($D$1:$R$350,MATCH("Debt Service Coverage",$C$1:$C$350,0),MATCH(MAX($D$1:$R$1),$D$1:$R$1,0))". I would simply change the "Debt Service Coverage" to the other corresponding metrics that I am interested in. I am also wondering if I could have this Summary workbook automatically update when opened and have the date that the data is being pulled from for each workbook as it may be different. Thank you in advance!

hi

I have a budget file with me.format of each sheet is as
follows

Gl code Budget Oct Nov DEC JAN...........SEPT
Total
0101120222 1200 100 100 100 100 ...........

total 20 Sheets in each file .I want to import this into
accounting Package for that purpose i need to copy all
these codes into one sheet and prepare a trail balance
monthwise.
how can i do this?
Your help is greatly appreciated

Thanks
rc

i had different sheets containing the same type of data in every sheet.what i want is to merge all data into one sheet.i had attached the sample sheet for reference.also let me know how to know the last cell in the data sheet

Hello. I'm sorry if this has been cover here already. I trying to combine multiple xls files into one sheet. I have a changing number of files (example named Jones.xls, Smith.xls, etc..) that I would like to combine so that the new sheet will show one file's data after another. I would also need to add a new column at the end that contains the name of the file xls that it came from. All the files have the same number of columns but differing number of rows. Unfortunaltely, due to work security, I can't attach the files I'll be working with. Any help would be appreciated.

I want to consolidate several sheet with same format and header line, into one sheet in the same workbook. Any suggestion with regards to any VBA-code I can use?


No luck finding an answer? You could always try Google.