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

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
>
>
>
>
>

Thanks - works a treat!
"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >
> >
> >
> >