We help IT Professionals succeed at work.

MS Excel - Refresh data in Summary worksheet from Data in Multiple Worksheets

204 Views
Last Modified: 2012-06-27
I have a summary worksheet called "Consolidate" that I'd like to have refreshed from multiple worksheets within the same workbook.  Data will be entered into 5 different sheets on a daily basis and we need to have a master list that will be cleared out (except for the first row that has the headers) and updated when a button is pushed.  However, there are sheets that need to be excluded called "Key" and "Master".  The "Master Sheet" has the button that will run the macros.  Data from each sheet will be stacked on top of each other (excluding the header row - Row 1) in the "Consolidate" worksheet.  Here is the order:
1)  Delete data from row 2 to end of data
2)  Bring in Data from sheet 1 (Row 2 through end of data)
3)  At the next available row enter Data from Sheet 2 (Row 2 through end of data)
4)  At the next available row enter Data from Sheet 3 (Row 2 through end of data)
5)  At the next available row enter Data from Sheet 4 (Row 2 through end of data)
6)  At the next available row enter Data from Sheet 5 (Row 2 through end of data)

I'm sure there's an easy way to do this with VB.  Thanks in advance!
Comment
Watch Question

I would recommend the RDB merge addin from Ron Debruin. Its free to download and only takes a second to add in. Would do exactly what you are looking for. It has a great user interface for merging workbooks, sheets.

Author

Commented:
That's a great Add-In!  However, since several people will be updating this file I think vb would be better.  That way the code can stay with the file no matter who opens it.

Author

Commented:
Can someone please help me with the vb code?
CERTIFIED EXPERT

Commented:
try this is Worksheet module code

assign the DoIt macro to the Button click.

Option Explicit
Dim rngConsolOut As Range
Sub DoIt()
    Dim sht As Worksheet
    
    ClearConsolidate
    
    For Each sht In ActiveWorkbook.Sheets
        Select Case sht.Name
        Case "Consolidate", "Key", "Master", "Master Sheet"
            'exclude these
        Case Else
            CopySht2Consol sht
        End Select
    Next sht
    
    Worksheets("Consolidate").Select
    Range("A2").Select

End Sub

Sub CopySht2Consol(shtx As Worksheet)
    Dim rngcopy As Range
    
    Set rngcopy = shtx.UsedRange.Offset(1, 0).Resize(shtx.UsedRange.Rows.Count - 1, shtx.UsedRange.Columns.Count)
    'copy the data to output
    rngcopy.Copy '(rngConsolOut.Worksheet.Name)
    
    rngConsolOut.PasteSpecial xlPasteValues
    
    'set next output zone
    Set rngConsolOut = rngConsolOut.Offset(rngcopy.Rows.Count, 0)
    
End Sub
Sub ClearConsolidate()
    Dim shtAction As Worksheet
    Set shtAction = Sheets("Consolidate")
    shtAction.UsedRange.Offset(1, 0).Resize(shtAction.Rows.Count - 1, shtAction.Columns.Count).Delete
    
    Set rngConsolOut = Sheets("Consolidate").Range("A2")
    
End Sub

Open in new window

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
For my interest,
What doesn't work with my code

Author

Commented:
Not impressed with the assistance I received.  I ended up having to figure it out on my own.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.