2007 - copy rows to master

[shondag]

Help please - - I have approx. 10 wrksheets with 1 as my master. They are all formatted the same with same column titles. As information is entered in a row in 1 or any of the 9 worksheets, I would like the information to be copied to the master worksheet in the next available row. Does this make sense? I am fairly good at Excel but know NOTHING about VBA. Being able to do this would make my NEW boss very happy - LOL. Hope I can get some help.

Shonda Gauthier
Lafayette, LA

 

[craig.penny]

Hi Shonda,

What will probably suit your needs is an event procedure like this one

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub

When you change a cell value in any sheet in the workbook this procedure will run. I'll try to help you figure out what to put in the code block. Can you describe the sheets to me? How many columns worth of data is tracked on each sheet, are there any formulas, and anything else that you think might be relevant?

 

[jbeaucaire]

I take the opposite approach.

1) I do not allow "typing" on my master sheet
2) I use a Worksheet_Activate macro on my master that "recollects all the data from the other sheets" each time you bring it up onscreen.
3) Usually I "sort" the Master by a certain column each time, too


Some basic code for you to play around with:

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet, LR As Long, NR As Long

Range("A2:A" & Rows.Count).EntireRow.ClearContents
NR = 2

For Each ws In Worksheets
    If ws.Name <> Me.Name Then
        LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
        ws.Range("A2:A" & LR).EntireRow.Copy Range("A" & NR)
        NR = NR + LR - 1
    End If
Next ws

Range("A1").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
End Sub

 

[craig.penny]

That's, indeed, a much cleaner approach. However, I don't see how that's opposite. They would both be event driven, no?

 

[jbeaucaire]

Wb_ShtChng method watches every sheets and pushes the data into the destination, that's what I surmised your full suggestion to be. This method works all the time as you work, the macro is constantly updating the master.

Mine works on only one sheet, the Master, and pulls the data into the sheet when you view it. So it's not active all the time, occurs once enmasse when bring the sheet up each time.


That's all I meant by opposite, pull vs push, all at once vs ongoing all the time.

I've used both methods with similar success, so I'm also not suggesting one is better than the other. Purely preference.

 

[craig.penny]

Ah yes I see your point with the pull vs. push explanation. I love a solution that didn't occur to me! I might actually learn something if I'm not careful

 

[shondag]

It is some basic information. Each Region will have their own spreadsheet and he wants it all to summarize on one sheet.

Date, Region, $$$, Manager, etc.

 

[jbeaucaire]

Did you try out the suggestion in post #3?

 

[shondag]

I would love to try these suggestions but it looks like an Alien landed on my computer. I do understand it is code but I need very basic instructions. I should copy this to???? Is it a macro?

 

[jbeaucaire]

Yes, it's a macro, an "event" macro that triggers itself each time you bring the Master sheet up onscreen.

1) Right-click the Master sheet tab (where you want the data collected) and select View Code
2) Paste the macro provided above into the sheet module that appears
3) Close the VBEditor and save your workbook

If you use Excel 2003 or earlier, just save the workbook normally,
If you use Excel 2007 or later, choose FILE TYPE: XLSM so the macros will work