Autogenerating entries on summary sheet...help

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Howdy All,

Here is the situation. I have a workbook with separate worksheets for major
accounts. These worksheets have various account data on them.

Here is what I would like to do. I want to create a summary worksheet that
will pull specific data off the account worksheets. But, I want the data on
the summary sheet to automatically generate based on the creation of each
account worksheet and pull data from specified cells. The specified cell
will always be the same on each account worksheet.

I hope I have explained this well enough to understand.

Thank you for your time and attention.
Brian
 
Thanks for the info Ron.

How do I get it to refresh for sheet name changes and new sheet additions?

Thanks,
 
Try this

This in the thisworkbook module

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.EnableEvents = False
Summary_All_Worksheets_With_Formulas_2
Application.EnableEvents = True
End Sub


And this macro in a module

Sub Summary_All_Worksheets_With_Formulas_2()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

Set Basebook = ThisWorkbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
On Error Resume Next
.DisplayAlerts = False
Basebook.Worksheets("Summary-Sheet").Delete
.DisplayAlerts = True
On Error GoTo 0
End With

Set Newsh = Basebook.Worksheets.Add

On Error Resume Next
Newsh.Name = "Summary-Sheet"

RwNum = 1
'The links to the first sheet will start in row 2

For Each Sh In Basebook.Worksheets
If Sh.Name <> Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1

'Newsh.Cells(RwNum, 1).Value = Sh.Name
'Copy the sheet name in the A column

For Each myCell In Sh.Range("A1,D5:E5,Z10") ' <----Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell
End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
 
Back
Top