Running macros in newly created sheets

[joecrabtree]

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

 

[Bernie Deitrick]

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]

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

 

[Bernie Deitrick]

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