Worksheet Info

has_1

New Member
Joined
Feb 9, 2005
Messages
2
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Robb

Board Regular
Joined
Feb 17, 2002
Messages
145
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

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

For the first part, this may be a start:
Code:
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

New Member
Joined
Feb 9, 2005
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

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