Worksheet Info

[has_1]

I have a question regarding data in several worksheets in an excel file. The sheets all have the same number of columns with headings but differing amounts of rows. What I would like to do is create a new sheet in the same file that will take the data from the other sheets and combine it into a master sheet. Also, if I were to add data/rows to one of the worksheet, I would like to have the master sheet update automatically. Has anyone done this before and if so how can it be done? Thanks in advance for all of your help.

 

[Robb]

Use your "Record Macro" buttton to move rows to a master worksheet. This code will help you understand what is needed to make this happen. Study the code!

Once you have coded your master you will then need to create a macro to "update" the master when rows are added to a given sheet. There are multiple ways of doing this (ie. create a menu, create a command button, create a tool bar, use right-click menu...to name a few).

I haven't given you any code, but hopefully I have kickstarted you to the world of VBA.

 

[Smitty]

Welcome to the Board!

For the first part, this may be a start:

Sub CopyData()
    Dim ws As Worksheet
    Dim LastRow As Range
                
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> "Summary" Then
                Set LastRow = Sheets("Summary").Range("A65536").End(xlUp)
                ws.UsedRange.Offset(1, 0).Copy LastRow.Offset(1, 0)
            End If
        Next ws
        
End Sub

It will copy the used range of each sheet, less the header row, to consecutive cells in a sheet called "Summary"

For the second part, you could probably use a Worksheet_Change event, but you would need to specify the range that would trigger the copy action and how you are "adding" rows. Are you actually inserting new rows or just appending data?

Hope that helps,

Smitty

 

[has_1]

Thanks

Thanks for the help. I am very new to macro programming so if you guys have any other good reference websites, I would appreciate it. Also to answer the question of new data, I just append to the file, no new line insertions.

Thanks again