To All,

I have a macro that creates a series of new worksheets from a master
worksheet. I.e. it splits a set of data into different worksheets.

On each worksheet there is a list of numbers in Column C. I want to be
able to calculate the average of this list of numbers for all the new
worksheets that the first macro creates. I then want these values to be
pasted into a sheet called 'FINAL DATA' ( which already exists ) Is
this possible? and if so how?

Thanks for your help,

Regards

Joseph Crabtree

Joseph,

Something like:

Sub AverageColumnC()
Dim mySh As Worksheet
For Each mySh In Worksheets
If mySh.Name < "FINAL DATA" Then
With Worksheets("Final Data").Range("A65536").End(xlUp)(2)
.Value = mySh.Name
.Offset(0, 1).Value = Application.Average(mySh.Range("C:C"))
End With
End If
Next mySh
End Sub

HTH,
Bernie
MS Excel MVP


"joecrabtree" wrote in message
ups.com...
To All,

I have a macro that creates a series of new worksheets from a master
worksheet. I.e. it splits a set of data into different worksheets.

On each worksheet there is a list of numbers in Column C. I want to be
able to calculate the average of this list of numbers for all the new
worksheets that the first macro creates. I then want these values to be
pasted into a sheet called 'FINAL DATA' ( which already exists ) Is
this possible? and if so how?

Thanks for your help,

Regards

Joseph Crabtree

Hello,

Thankyou very much for that. That works great. Just one further
question - If I want it to ignore more spreadsheets other than "FINAL
Data" how can I add this? For example if I want to ignore "Final Data
2" as well?

Thanks allot

Joseph

On Nov 28, 2:38 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Joseph,

Something like:

Sub AverageColumnC()
Dim mySh As Worksheet
For Each mySh In Worksheets
If mySh.Name < "FINAL DATA" Then
With Worksheets("Final Data").Range("A65536").End(xlUp)(2)
.Value = mySh.Name
.Offset(0, 1).Value = Application.Average(mySh.Range("C:C"))
End With
End If
Next mySh
End Sub

HTH,
Bernie
MS Excel MVP

"joecrabtree" wrote in oglegroups.com...



To All,

I have a macro that creates a series of new worksheets from a master
worksheet. I.e. it splits a set of data into different worksheets.

On each worksheet there is a list of numbers in Column C. I want to be
able to calculate the average of this list of numbers for all the new
worksheets that the first macro creates. I then want these values to be
pasted into a sheet called 'FINAL DATA' ( which already exists ) Is
this possible? and if so how?

Thanks for your help,

Regards

Joseph Crabtree- Hide quoted text -- Show quoted text -

Joseph,

The best way depends on your naming convention. For example, you could use this to ignore all
sheets whose name starts with Final

If Left(mySh.Name,5) < "FINAL" Then

or you could use this to only process sheets with names that start with Data

If Left(mySh.Name,4) = "Data" Then

Since you control the new sheet addition, you can control the naming as well....

HTH,
Bernie
MS Excel MVP


"joecrabtree" wrote in message
ups.com...
Hello,

Thankyou very much for that. That works great. Just one further
question - If I want it to ignore more spreadsheets other than "FINAL
Data" how can I add this? For example if I want to ignore "Final Data
2" as well?

Thanks allot

Joseph

On Nov 28, 2:38 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Joseph,

Something like:

Sub AverageColumnC()
Dim mySh As Worksheet
For Each mySh In Worksheets
If mySh.Name < "FINAL DATA" Then
With Worksheets("Final Data").Range("A65536").End(xlUp)(2)
.Value = mySh.Name
.Offset(0, 1).Value = Application.Average(mySh.Range("C:C"))
End With
End If
Next mySh
End Sub

HTH,
Bernie
MS Excel MVP

"joecrabtree" wrote in
oglegroups.com...



To All,

I have a macro that creates a series of new worksheets from a master
worksheet. I.e. it splits a set of data into different worksheets.

On each worksheet there is a list of numbers in Column C. I want to be
able to calculate the average of this list of numbers for all the new
worksheets that the first macro creates. I then want these values to be
pasted into a sheet called 'FINAL DATA' ( which already exists ) Is
this possible? and if so how?

Thanks for your help,

Regards

Joseph Crabtree- Hide quoted text -- Show quoted text -