VBA to sum cell H25 from all worksheets named Factors Shet () and divide by the total number of sheets name Factors Sheet ()

[Marc Wylie]

Hi All,

I'm Trying to write a VBA Code to sum all values from Cell H25 in sheets named Factors Sheet () and divide by the total number of sheets by that name. and place the result on a cell within a sheet called dashboard.

I know how to do it for all active sheets but my issue is that the number of Sheets could be from Factors Sheet (1) through to Factors Sheet (200) or an indefinite number, and as the intent of the formula is to average the total from only sheets named Factors Sheet () I cant just use all active sheets as it will skew the result

Thanks for your time (again)

Marc

 

[FormR]

Your variable declarations should be within the sub()

i.e.

Sub Calc()
Dim ws As Worksheet, i As Variant, j As Variant, Dash As Integer, Div As Integer
'Rest of code

Google "Scope of variables in VBA" for more details.

 

[FormR]

Marc, as a side note - if you have modified code that has been suggested by the forum it is always best to post that new code when reporting problems.

I recommend that you spend a bit of time reading up on variable data types as well as variable scope and re-consider some of your declarations.

 

[Marc Wylie]

Hi Sorry, I really didn't mean to break any rules.

I need to get this done and written up by the end of August, and I keep coming across huge problems.

Up until the night I posted the new code the code hadn't changed

Sorry again

Marc