[rsdjimbie]

I need to to copy and paste to multiple sheets where the sheet names are not static.

All it involves is the copy and paste of a formula to all the sheets in the workbook without having to refer to every book as the names of these change.
Code I am using currently =

ActiveWorkbook.Sheets.Select
Range("M3").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]="""",RC[-1],IF(COUNTIF(R1C[-8]:RC[-8],RC[-8])>1,"""",SUMIF(R1C[-8]:R[25]C[-8],RC[-8],R1C[-1]:R[25]C[-1])))"
Range("
Range(").Select
Selection.Copy
Range("M4:M100").Select.Paste
Application.CutCopyMode = False]

But the macro will not copy the formula to the specified ranges.

Thanks!

[rsdjimbie]

Solved with this decleration; If sheets <> "Blah"

Dim was As Worksheet

For Each was In Worksheets
If was.Name <> "Blah" Then '<-If sheet name is not "blah" then it will select all other sheets!
was.Range("M3").FormulaR1C1 = "=IF(RC[-8]="""",RC[-1],IF(COUNTIF(R1C[-8]:RC[-8],RC[-8])>1,"""",SUMIF(R1C[-8]:R[25]C[-8],RC[-8],R1C[-1]:R[25]C[-1])))"

Works a charm.

[JBeaucaire]

Nicely done.

Tip: here in the forum use [CODE] tags around your code instead of [QUOTE] tags and it will format/preserve all your nice indentations. ;)

[rsdjimbie]

Thanks, but don't have code tags, only quote, will contact admin.

[rsdjimbie]

And even easier;

For Each was In ActiveWorkbook.Worksheets '<-Use this statement to
Was.Activate ' run code on all sheets.

[JBeaucaire]

1) I would never recommend using was.Activate like that. Significant speed reduction when adding selecting and activating unnecessarily.

TIP: Shorter code does not equal better code. Many people can write a nice short "loop" that's only 3-4 lines of code long, and I can replace it with a non-loop 10-12 lines of code that is extremely fast on a 1000 rows of data.

1000 rows in a loop is 3000-4000 lines of vba activity in that loop, but my non-loop still processes all 1000 rows in 10 actions. You see the possible gain?

Anyway, I would stick with not be selecting.

2) The CODE tags can be manually entered using the same format you see/use for the QUOTE tags, just change the word "QUOTE" to "CODE" in the same manner.

Or, click on GO ADVANCED and you'll see the # icon for code tags.