Consolidate Data From Many Sheets To One Sheet

tqv1969 · Sep 30th 2006

Dear Gurus,

I'am have one data file with so many sheets those need to edit and bring them to final summary sheet.
I dont know how to move between sheets and edit them automatically.
Editing will need to add the brand name next to the variance name and the sheet name in one new column. And bringing those rows with completed data only to All sheet from those sheets in workbook.
Your help is very much appreciated.
Best regards,
TQV1969

xlite · Sep 30th 2006

Re: Consolidate Data From Many Sheets To One Sheet

hi TQV1969,

have a look at my example and try to adapt it to your situation

hope this helps

tqv1969 · Sep 30th 2006

Re: Consolidate Data From Many Sheets To One Sheet

Dear Xlite,

That's code is great that help me to move between sheets. I've got only one problem left: how can i get the name of sheet fill in automatically in Sheet1 and Sheet2 as your example file?
Some body can help?

Best regards,
TQV1969

xlite · Sep 30th 2006

Re: Consolidate Data From Many Sheets To One Sheet

hi TQV1969,

adjust the code to:

Code

Sub Summarize()
Dim ws As Worksheet
Dim lastRng As Range
Application.ScreenUpdating = False 'speed up code
For Each ws In ThisWorkbook.Worksheets
Set lastRng = ThisWorkbook.Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)


    Select Case ws.Name
        Case "Summary" 'exlude
        'do nothing
        Case Else
        ws.Activate
                
        'copy data from individual sheets
        Range("A2", Range("D65536").End(xlUp)).Copy lastRng.Offset(0, 1)


         'add sheet name before data
        For i = 0 To Range("A2", Range("A65536").End(xlUp)).Count - 1
        lastRng.Offset(i, 0) = ws.Name
        Next
        
    End Select
Next
Application.CutCopyMode = False 'clear clipboard
Application.ScreenUpdating = True
Sheets("Summary").Activate
End Sub

Display More

hope this helps

tqv1969 · Sep 30th 2006

Re: Consolidate Data From Many Sheets To One Sheet

Dear xlite

It's helpful and I've just modified a little. Many thanks:)
btw, could you please show me how can I assign the macro to one drawing item like in your example?

TQV1969,

xlite · Sep 30th 2006

Re: Consolidate Data From Many Sheets To One Sheet

ah that's easy,

from the drawing toolbar, place any shape into the worksheet.
right click it and assign macro, choose yr macro and that's it!

tqv1969 · Sep 30th 2006

Re: Consolidate Data From Many Sheets To One Sheet

Dear xlite,

Yes, that's easy,
Many thanks!!!!!
TQV1969

xlite · Sep 30th 2006

Re: Consolidate Data From Many Sheets To One Sheet

you're most welcome

Consolidate Data From Many Sheets To One Sheet

Participate now!

Share

Users Viewing This Thread