Create Summary Sheet
 

I have alot of worksheets that I would like to create a summary sheet for.

The summary sheet will have the following info

Worksheet Name in one column and the value from Cell G24 in the next column

Eg
Worksheet ABC1 Value G24 = £100
Worksheet CDE2 Value G24 = £150

Summary Sheet will look like
ABC1 £100
CDE2 £150

Is it possible to do this through code or do I have to manually go into each
individual sheet?

Thanks

Create Summary Sheet
 

Here is some code that should be close...

Sub PopulateSummarySheet()
Dim wks As Worksheet
Dim wksSummary As Worksheet
Dim rngCurrent As Range

Set wksSummary = Sheets("Summary")
wksSummary.Cells.Delete
Set rngCurrent = wksSummary.Range("A1")

rngCurrent.Value = "Sheet"
rngCurrent.Offset(0, 1).Value = "Amount"
Set rngCurrent = rngCurrent.Offset(1, 0)
For Each wks In Worksheets
If wks.Name < wksSummary.Name Then
rngCurrent.Value = wks.Name
rngCurrent.Offset(0, 1).Value = wks.Range("G24").Value
Set rngCurrent = rngCurrent.Offset(1, 0)
End If
Next wks

End Sub
--
HTH...

Jim Thomlinson


"AliH" wrote:

I have alot of worksheets that I would like to create a summary sheet for.

The summary sheet will have the following info

Worksheet Name in one column and the value from Cell G24 in the next column

Eg
Worksheet ABC1 Value G24 = £100
Worksheet CDE2 Value G24 = £150

Summary Sheet will look like
ABC1 £100
CDE2 £150

Is it possible to do this through code or do I have to manually go into each
individual sheet?

Thanks

Create Summary Sheet
 

Thanks - works a treat!
"Jim Thomlinson" wrote in message
...
Here is some code that should be close...

Sub PopulateSummarySheet()
Dim wks As Worksheet
Dim wksSummary As Worksheet
Dim rngCurrent As Range

Set wksSummary = Sheets("Summary")
wksSummary.Cells.Delete
Set rngCurrent = wksSummary.Range("A1")

rngCurrent.Value = "Sheet"
rngCurrent.Offset(0, 1).Value = "Amount"
Set rngCurrent = rngCurrent.Offset(1, 0)
For Each wks In Worksheets
If wks.Name < wksSummary.Name Then
rngCurrent.Value = wks.Name
rngCurrent.Offset(0, 1).Value = wks.Range("G24").Value
Set rngCurrent = rngCurrent.Offset(1, 0)
End If
Next wks

End Sub
--
HTH...

Jim Thomlinson


"AliH" wrote:

I have alot of worksheets that I would like to create a summary sheet
for.

The summary sheet will have the following info

Worksheet Name in one column and the value from Cell G24 in the next
column

Eg
Worksheet ABC1 Value G24 = £100
Worksheet CDE2 Value G24 = £150

Summary Sheet will look like
ABC1 £100
CDE2 £150

Is it possible to do this through code or do I have to manually go into
each
individual sheet?

Thanks