PDA

View Full Version : HELP! Need code to Update and Get new information



isotopes
08-08-2011, 09:23 AM
Hello all,

My organization is looking to build a database in excel(2007) that allows for detailed information to be stored for each sub-business on worksheets within separate large business workbooks. (Variable number of worksheets in 19 total workbooks stored on a shared drive.) The first worksheet of each workbook is a summary of that particular business lines key information. There will never be more than the current number of workbooks, though more worksheets may be added.

I need to come up with a way to consolidate the summary pages and have them update if new information is added or old information is changed. I also have to come up with a way to get all the information store on the worksheets into one large data pool.

I have a very limited knowledge of VBA and would greatly appreciate any and all help.

Thanks!

Jeff

*Attached is an example of the workbook - summary worksheet and detailed worksheet*

Bob Phillips
08-08-2011, 10:48 AM
Put this in the ThisWoprkbook code module




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

If Not Sh.Name Like "Department *" Then

On Error Resume Next
Set cell = Worksheets(1).Columns(1).Find(Target.Value)
On Error GoTo 0
If cell Is Nothing Then

With Worksheets(1).Range("A1").End(xlDown).Offset(1, 0)

.Offset(0, 0).Formula = "='" & Sh.Name & "'!$A$3"
.Offset(0, 1).Formula = "='" & Sh.Name & "'!$D$3"
.Offset(0, 2).Formula = "='" & Sh.Name & "'!$B$8"
.Offset(0, 3).Formula = "='" & Sh.Name & "'!$B$9"
.Offset(0, 4).Formula = "='" & Sh.Name & "'!$B$17"
.Offset(0, 5).Formula = "='" & Sh.Name & "'!$C$37"
.Offset(0, 6).Formula = "='" & Sh.Name & "'!$F$37"
.Offset(0, 7).Formula = "='" & Sh.Name & "'!$C$25"
.Offset(0, 8).Formula = "='" & Sh.Name & "'!$C$26"
.Offset(0, 9).Formula = "='" & Sh.Name & "'!$C$27"
.Offset(0, 10).Formula = "='" & Sh.Name & "'!$C$28"
.Offset(0, 11).Formula = "='" & Sh.Name & "'!$C$29"
.Offset(0, 12).Formula = "='" & Sh.Name & "'!$C$30"
.Offset(0, 13).Formula = "='" & Sh.Name & "'!$E$30"
.Offset(0, 14).Formula = "='" & Sh.Name & "'!$F$30"
.Offset(0, 15).Formula = "='" & Sh.Name & "'!$G$30"
.Offset(0, 16).Formula = "='" & Sh.Name & "'!$C$31"
.Offset(0, 17).Formula = "='" & Sh.Name & "'!$C$33"
.Offset(0, 18).Formula = "='" & Sh.Name & "'!$C$33"
.Offset(0, 19).Formula = "='" & Sh.Name & "'!$C$34"
.Offset(0, 20).Formula = "='" & Sh.Name & "'!$C$35"
End With
End If
End If
End Sub