I have coding that pulls data from 70-80 worksheets into a summary sheet (the
number changes every month), and that's worked fine for quite a while, but
others are going to be using this workbook now, and I'd like for them to be
able to make changes on the worksheets and have it update the summary when
they do. The coding below pulls just the values in. Can anybody tell me what
I need to do to have it link to the cells instead of just copying them? Or do
I need to just start over? Thank you for your help.


Sub Summary()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

Application.ScreenUpdating = False
Application.EnableEvents = False

'Delete the sheet "Summary" if it exists
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Summary"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name Then

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.Range("O9:R44")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destination Sheet"
GoTo ExitTheSub
End If

'This copies all values/formats

With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

End If
Next

ExitTheSub:

Application.GoTo DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200912/1

See
http://www.rondebruin.nl/summary.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Joe_Hunt via OfficeKB.com" <u45578@uwe wrote in message news:a0bba85848f54@uwe...
I have coding that pulls data from 70-80 worksheets into a summary sheet (the
number changes every month), and that's worked fine for quite a while, but
others are going to be using this workbook now, and I'd like for them to be
able to make changes on the worksheets and have it update the summary when
they do. The coding below pulls just the values in. Can anybody tell me what
I need to do to have it link to the cells instead of just copying them? Or do
I need to just start over? Thank you for your help.


Sub Summary()

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

Application.ScreenUpdating = False
Application.EnableEvents = False

'Delete the sheet "Summary" if it exists
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Summary"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name Then

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.Range("O9:R44")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destination Sheet"
GoTo ExitTheSub
End If

'This copies all values/formats

With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

End If
Next

ExitTheSub:

Application.GoTo DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200912/1

That is perfect! I really do appreciate it.

Ron de Bruin wrote:
See
http://www.rondebruin.nl/summary.htm

I have coding that pulls data from 70-80 worksheets into a summary sheet (the
number changes every month), and that's worked fine for quite a while, but
[quoted text clipped - 62 lines]

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200912/1