combining data from multiple worksheets

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
would anyone be able to write the code that will combine the data from all rows from all worksheets within a workbook. i've struggled with this one....

here is the deal:

all worksheets have the same columns and column headings but differnet amount of row counts. the width of the sheets is to Column "M" or "13" and there are no blank columns.

There is no need to have the columns headings repeat within the compiled worksheet.

the amount of worksheets will vary depending on when i run it so it will not be able to use specific naming conventions.

thansk so much who help with this one!!!!!

tuktuk
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Or this. Requires the worksheets to be copied are located after a worksheet named "after here" and before a worksheet named "before here". Fazza

Code:
Sub PutItAllOnOneWorksheet()
    
    'Takes data from successive worksheets, located after the worksheet
    'called "after here" and before the worksheet called "before here",
    'and puts it all together onto one new worksheet in a new workbook.

    Dim lCounter As Long
    Dim lHowManyRowsToCopy As Long
    Dim lMaxRowsOnWks As Long
    Dim lRowToPasteFromLast As Long
    Dim lRowToPasteTo As Long
    Dim wbkNew As Workbook
    
    Application.ScreenUpdating = False
    
    lHowManyRowsToCopy = 1
    lMaxRowsOnWks = Cells.Rows.Count
    lRowToPasteTo = 1
    
    Set wbkNew = Application.Workbooks.Add(xlWBATWorksheet)
    wbkNew.Worksheets(1).Name = "combined data"
    
    With ThisWorkbook
        
        .Worksheets(.Worksheets("after here").Index + 1).Rows(lHowManyRowsToCopy).Copy _
            wbkNew.Worksheets(1).Rows(lRowToPasteTo)
        
        lRowToPasteTo = lRowToPasteTo + 1
        
        For lCounter = .Worksheets("after here").Index + 1 To .Worksheets("before here").Index - 1
            With .Worksheets(lCounter)
                With .Range("A1").CurrentRegion
                    lHowManyRowsToCopy = .Rows.Count - 1
                    If lRowToPasteTo + lHowManyRowsToCopy > lMaxRowsOnWks Then GoTo TooMuchData
                    .Offset(1).Resize(lHowManyRowsToCopy).Copy wbkNew.Worksheets(1).Cells(lRowToPasteTo, 1)
                    lRowToPasteTo = lRowToPasteTo + lHowManyRowsToCopy
                End With
            End With
        Next lCounter
        Set wbkNew = Nothing
    End With
    Application.ScreenUpdating = True
    MsgBox "Here is the new file!!"
    Exit Sub
    
TooMuchData:
    Application.DisplayAlerts = False
        wbkNew.Close
    Application.DisplayAlerts = True
    
    MsgBox Prompt:="Data doesn't fit within Excel's row limit." & vbCr & vbCr & "[" & _
        Format$(lMaxRowsOnWks, "#,##0") & " rows]", _
        Buttons:=vbCritical, Title:="Can't do it, sorry!"
    
End Sub
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
hey thanks....this code work perfectly.

to take it one step further, now i am hoping to copy all of the info from Worksheet("Master") to another excel file called "POQuantityTrackingReport" in the Worksheet("CompiledMaster").

and then continue to update this "CompiliedMaster" worksheet everytime i run the above code. it is essentially using the worksheet as a database.

does this make sense? any suggestions/help is greatly appreciated.

Tuktuk
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856

ADVERTISEMENT

i was able to figure out the update teh database request from above....


ONE MORE QUESTION THOUGH.........

i am using the above code to combine data from all worksheet to a "Master" worksheet.

BUT, i have a column "E" that contain a PO Number. Here is an Example: 07-6669.

The problem is when the code runs an pastes entries with such PO number it automatically changes the cells format to Custom; mmm,yy.

So the Master sheets changes "07-6669" to "Jul-69".

How can i solve this issue.......
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi Tuktuk,

I'm not sure. I don't fully understand.

The code simply copies what is already there. If I manually make a cell entry 07-6669 it is instantly showing as a date. So, the code would copy that to be a date. If the entry was existing already as text, then copying it retains it as text.

If this is you have different behaviour, then how does the initial value 07-6669 get created?

I guess there are several approaches. Maybe you could try handling the data using database style functionality. So, instead of copying from one worksheet to another use some ADO or alternative and some SQL. A less elegant and generic way would be to use code to force the format of the particular column that contains text. (Another way might be even to just go to MS Access and do the work?)

Here is some code using a query table (as an alternative to ADO).

This might not be helpful as I don't know how the text entries are created and why Excel is handling them as numbers..

The SQL uses "SELECT *", please modify to suit if the fields on all your worksheets are not identical or if you don't want to select all fields.

HTH, Fazza
Code:
Sub CombineDataViaQT()

    Dim arDataSheetNames() As String
    Dim arSQL_Elements() As String
    Dim i As Long, j As Long
    Dim lWhichOne As Long
    
    Dim sConn As String
    Dim sSQL As String
    Dim sSQL_SELECT As String
    Dim sSQL_FROM As String
    Dim sSQL_UNION_ALL As String
    
    Dim wbkNew As Workbook
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook
        
        'Assumes sheets named "after here" & "before here" with data sheets in between.
        ReDim arDataSheetNames(1 To .Worksheets("before here").Index - .Worksheets("after here").Index - 1)
        
        'Each worksheet has one data table and each contributes 3 elements to the SQL, except
        'the first data table which has 2 elements only. [SQL construct "SELECT * FROM tbl1"
        'for the first file and then the three elements for each other worksheet,
        '"UNION ALL SELECT * FROM tb2", etc.]
        ReDim arSQL_Elements(1 To 3 * UBound(arDataSheetNames) - 1)

        'Use new counter to simplify references. The data worksheet indices don't start
        'at 1 because they aren't the first worksheets and this is one way to get the
        'required references within the loop just below.
        lWhichOne = 0
        'Loop through worksheets
        For i = .Worksheets("after here").Index + 1 To .Worksheets("before here").Index - 1
            lWhichOne = lWhichOne + 1
            'Load data worksheet names to an array for use when creating the SQL
            arDataSheetNames(lWhichOne) = .Worksheets(i).Name
            'Assign named ranges to data ranges
            .Worksheets(i).Range("A1").CurrentRegion.Name = "tbl" & lWhichOne
        Next i

        'Connection for query
        sConn = Join$(Array("ODBC;DSN=Excel Files;DBQ=", .FullName, ";DefaultDir=", _
                    .Path, ";DriverID=790;MaxBufferSize=2048;PageTimeout=5;"), vbNullString)
    End With
    
    'Generate SQL, in two steps
    'First the combination from the multiple sheets
    sSQL_SELECT = "SELECT '"
    sSQL_FROM = "FROM tbl"
    sSQL_UNION_ALL = "UNION ALL"

    arSQL_Elements(1) = sSQL_SELECT & arDataSheetNames(1) & "' AS [source], *"
    arSQL_Elements(2) = sSQL_FROM & "1"

    For j = 2 To UBound(arDataSheetNames)
        arSQL_Elements(3 * j - 3) = sSQL_UNION_ALL
        arSQL_Elements(3 * j - 2) = sSQL_SELECT & arDataSheetNames(j) & "' AS [source], *"
        arSQL_Elements(3 * j - 1) = sSQL_FROM & j
    Next j
    'Hence the SQL to combine the data from multiple sheets
    sSQL = Join$(arSQL_Elements, vbCr)
'    Debug.Print sSQL
        
    Set wbkNew = Application.Workbooks.Add(xlWBATWorksheet)
    With wbkNew.Worksheets(1)
        .Name = "combined data"
        With .QueryTables.Add(Connection:=sConn, Destination:=.Range("A1"), Sql:=sSQL)
            .Refresh BackgroundQuery:=False
        End With
        .Cells.NumberFormat = "#,##0"
    End With
    Set wbkNew = Nothing
    
    Application.ScreenUpdating = True
    MsgBox "Here is the new file!!"
End Sub
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856

ADVERTISEMENT

hmmmmmm well let me state that the data is part of an export from MS QuickBooks to excel.

here are a few more specifics to assist in helping us figure out why this is happening

all data export is pasted as TEXT........this includes data that appears as "CustomerONE", PO No = "27055-QW" and Inventory No = "18005412".

NOTE that the Inventory No have a GREEN Tag on them with an exclamation point attached....stating saved as text.

WHEN THE Sub PutItAllOnOneWorksheet() is ran the data on Sheet("Master") is FORMATED TO GENERAL.....that is if i rt click>formatCellls> the Category = General

EXCEPT for SOME DATA is the following format (example) 07-5559.

this data is formated as "Custom" where Type = "mmm-yy".

does that help.

might you have any other suggestions?

this one is getting to me becasue i have to manually go to the sheet master and change the format to TEXT and then reenter the data.
 

eturner

New Member
Joined
Aug 24, 2007
Messages
1
Hi -

I'm having similiar problems using the CopyFromWorksheets()
link stanleydgromjr posted. I have a column with numbers stored as text (with leading zeros), however, post macro the data is stored as numbers (losing the leading zeros). Can anyone lend a hand?

Thanks!
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
hmmmmm, i am still struggling with this one......

i can provide data to assist in figuring this one out, if needed.

in short, i've used the above CopyFromWorksheets() code which works perfect except i have a field that is in the following format "07-6669".....so when the code runs it add the data but formats this data differently/chages the data to be something like "19807777".

is there a way to solve this?
 

winshent

New Member
Joined
Jun 30, 2008
Messages
19
Is it possible to combine data of multiple worksheets.

I am going to be working with approx 2 million rows of raw data in Excel 2007. My original idea was to use Access for storing the data and then create reporting outputs in either Access or Excel.

But if data can be spread across multiple sheets and analysed then i would like to explore that as well.

Cheers

Vince
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,448
Members
417,209
Latest member
Agbarker

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