Merging multiple sheets into one

alon

Board Regular
Joined
Apr 12, 2011
Messages
58
Hello all,
I have an excel spreadsheet with many sheets. All sheets are structured the same (same number of columns, same variables, same formatting, etc.).

I need to combine the data in all sheets into one, when the data from each is just copied one below the other.

Is it possible to do so?

I uploaded a sample here: http://dl.dropbox.com/u/7112203/example-file-2-stores.xlsx

Basically, what I have done was to combine the first two sheets (London, Sheffield, into the third one).

Thanks in advance!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
alon

If the combined sheet is the last in the file, this code will work:

Code:
Sub moving_data()
    
    Dim ws As Worksheet
    
    With ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        .UsedRange.Offset(1).ClearContents
        For Each ws In .Parent.Worksheets
            If ws.Index < .Index Then
                ws.UsedRange.Offset(1).Copy Destination:=.Range("B" & .Rows.Count).End(xlUp).Offset(1)
                .Range("A2", .Range("B" & .Rows.Count).End(xlUp).Offset(, -1)) = ws.Name
            End If
        Next
    End With

End Sub
 

alon

Board Regular
Joined
Apr 12, 2011
Messages
58
Yea.... So I'm going to have to ask a noob's question...
ahm.. well silly me, but where do I run this code?
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Okay, good night too.
 

alon

Board Regular
Joined
Apr 12, 2011
Messages
58

ADVERTISEMENT

Sorry, it doesn't work.
Maybe I'm running it wrong, but what it does is to just take one sheet and do the required operation on the last open sheet.
It doesn't do it for all sheets.
 

juliano

New Member
Joined
Nov 11, 2011
Messages
21
Hi everyone!!

This VBA can be use for merge sheets into one AND/OR merge worksheets into one (if you have more than one *.xls in your folder)
All *.xls have to be in a folder named "plans"

I'm glad if i can help.....

Regards!!!

Code:
Sub CombineSheetsFromAllFilesInADirectory()
   
     
    Dim Path            As String 'string variable to hold the path to look through
    Dim FileName        As String 'temporary filename string variable
    Dim tWB             As Workbook 'temporary workbook (each in directory)
    Dim tWS             As Worksheet 'temporary worksheet variable
    Dim mWB             As Workbook 'master workbook
    Dim aWS             As Worksheet 'active sheet in master workbook
    Dim RowCount        As Long 'Rows used on master sheet
    Dim uRange          As Range 'usedrange for each temporary sheet
     
     '***** Set folder to cycle through *****
    Path = ThisWorkbook.Path & "\plans\" 'Change as needed, ie "C:\"
     
    Application.EnableEvents = False 'turn off events
    Application.ScreenUpdating = False 'turn off screen updating
    Set mWB = Workbooks.Add(1) 'create a new one-worksheet workbook
    Set aWS = mWB.ActiveSheet 'set active sheet variable to only sheet in mWB
    If Right(Path, 1) <> Application.PathSeparator Then 'if path doesnt end in "\"
        Path = Path & Application.PathSeparator 'add "\"
    End If
    FileName = Dir(Path & "*.xls", vbNormal) 'set first file's name to filename variable
    Do Until FileName = "" 'loop until all files have been parsed
        If Path <> ThisWorkbook.Path Or FileName <> ThisWorkbook.Name Then
            Set tWB = Workbooks.Open(FileName:=Path & FileName) 'open file, set to tWB variable
            For Each tWS In tWB.Worksheets 'loop through each sheet
                Set uRange = tWS.Range("A2", tWS.Cells(tWS.UsedRange.Row + tWS.UsedRange.Rows _
                .Count - 1, tWS.UsedRange.Column + tWS.UsedRange.Columns.Count - 1)) 'set used range
                If RowCount + uRange.Rows.Count > 65536 Then 'if the used range wont fit on the sheet
                    aWS.Columns.AutoFit 'autofit mostly-used worksheet's columns
                    Set aWS = mWB.Sheets.Add(After:=aWS) 'add a new sheet that will accommodate data
                    RowCount = 0 'reset RowCount variable
                End If
                If RowCount = 0 Then 'if working with a new sheet
                    aWS.Range("A1", aWS.Cells(1, uRange.Columns.Count)).Value = _
                    tWS.Range("A1", tWS.Cells(1, uRange.Columns.Count)).Value 'copy headers from tWS
                    RowCount = 1 'add one to rowcount
                End If
                aWS.Range("A" & RowCount + 1).Resize(uRange.Rows.Count, uRange.Columns.Count).Value _
                = uRange.Value 'move data from temp sheet to data sheet
                RowCount = RowCount + uRange.Rows.Count 'increase rowcount accordingly
            Next 'tWS
            tWB.Close False 'close temporary workbook without saving
        End If
        FileName = Dir() 'set next file's name to FileName variable
    Loop
    aWS.Columns.AutoFit 'autofit columns on last data sheet
    mWB.Sheets(1).Select 'select first data sheet on master workbook
    Application.EnableEvents = True 're-enable events
    Application.ScreenUpdating = True 'turn screen updating back on
     
     'Clear memory of the object variables
    Set tWB = Nothing
    Set tWS = Nothing
    Set mWB = Nothing
    Set aWS = Nothing
    Set uRange = Nothing
End Sub
 

alon

Board Regular
Joined
Apr 12, 2011
Messages
58
Hi Juliano,
Thanks for your help.
What I need is to combine the different sheets that appear in the same file, not multiple files.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Where did you paste the code alon? In what file, and in what object in VBA?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,494
Messages
5,596,486
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top