Importing an excel sheet into another

**Kapellu2** · 11-04-2010, 03:11 PM

Hey everyone,

I am trying to combine a bunch of summary sheets into one main sheet and was curious as to how to approach it.

Basically I have one common summary sheet filled in for about 100 different products. I was wondering if I could open up a new excel sheet, and somehow import the 100 different summary sheets into one large sheet.

Any help would be greatly appreciated.

**psrs0810** · 11-05-2010, 08:32 AM

Kapellu2

Take a look at http://excel.tips.net/Pages/T003005_..._Into_One.html

There is a macro in there that takes multiple sheets into 1 sheet.
Read the commentary to see if that will be the best resolution

HTH

**royUK** · 11-05-2010, 09:07 AM

That suggested code is very ineficient. try this

Option Explicit

Sub Combine()
    Dim ws As Worksheet
    Dim rRng As Range
    Dim bHeading As Boolean
    On Error Resume Next
   
    Worksheets.Add before:=Worksheets(1) ' add a sheet in first place
    ActiveSheet.Name = "Combined"

    ' work through sheets
    For Each ws In ThisWorkbook.Worksheets
     Set rRng = ws.UsedRange
     Select Case ws.Name
     Case "Combined" 'do nothing
      Case Else
     With Worksheets("Combined")
       If Not bHeading Then
       rRng.Copy Destination:=.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
       Else: rRng.Offset(1, 0).Resize(rRng.Rows.Count - 1).Copy _
       Destination:=.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
      End If
    End With
       bHeading = True
       End Select
    Next
    On Error GoTo 0
End Sub