Link Worksheets Into Master

[Guest]

Using Excel 2003 - We have worksheets with tabs called 'Sec 10', 'Sec 11',
etc. (up to Sec 61) that are updated by the individuals that work in those
sections. We would like to link all of those worksheets into a master that
will show all data in each section consolidated into one (with MANY rows!),
and that will continually update the master as data is entered in each
worksheet. All worksheets have the same column headers in Row 1 (NHBD, SEC,
BLK, LOT, DATE, WHO, STATUS, NOTES). We would also like to keep the Master
sorted by SEC then BLK, then LOT order.

I don't know VBA or any advanced formulas, and have tried using
consolidation, normal linking (which produces a number, not the individual
rows of data). The people using this don't know how to use or have Access,
so I need to figure out a simple way to do this in Excel. THANK YOU!

 

[Ron de Bruin]

See my site for a few examples
For example this one
http://www.rondebruin.nl/copy2.htm

 

[Guest]

I was able to use the ALT F11 and see where to put the code, so that was a
big help. I read through your examples, but since I don't know much about
advanced commands in Macros, SQL or VBA, I have no idea what some of this
does.

It looks like I should use the part that says "Copy from Row 2 till the last
row with data", since they will be continually filling in the individual
worksheets. Where it says "ThisWorkbook.Worksheets ("MergeSheet")" should I
be replacing that with names of all the tabs, or does the program just know
because the code is in that workbook? Does the data in each tab need to have
a range name first? Do I have to also include "Common Functions required for
all routines" to make this work?

Thank you for your help!

 

[Ron de Bruin]

Hi

Copy this in a normal module of your workbook

Alt F11
Insert module
Pste the code
Alt q to go back to excel

When you use Alt F8 in Excel run the macro
You not have to chnage things in the code

Sub Test2()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies everything, if you only want to copy
'values/formats look at the example below the first example
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A")

End If
Next

Application.Goto DestSh.Cells(1)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[Guest]

Hi Ron!

Thank you so much for your help! That code worked perfectly, and we have a
bunch of "happy campers" here. I have got to learn how to do that myself
someday.

Take care,
PolQueen

 

[Ron de Bruin]

Hi PolQueen

Thanks for the feedback