Enter Multiple Formulas At Once

[Chris Waller]

Dear All,

I have a large number of Spreadsheets contained in a Workbook Approximately four Spreadsheets are linked to all the others using formulae. What I would like to know is what is the best\easiest way to link all these Spreadsheets rather than inputting each individual formula manually? Every time I create a new spreadsheet I have to input 22 different formulae to link it to the other spreadsheets. TIA

 

[portews]

I assume you're doing something like pasting lookup links on your 4 spreadsheets to look at the new page?

It can be something as simple as setting the new cells equal to the formula you want. Make the new sheet active to get the ActiveSheet.Name and run the macro.

Something like this:

Sheets("Sheet1").Range("A1") = "=" & ActiveSheet.Name & "!A1"
Sheets("Sheet2").Range("B9") = "=" & ActiveSheet.Name & "!C6"
etc.

I'm sure you'll have to do more code to position things where you need them to be automatically for each new sheet and depending on your formulas.

You can also take a look at PasteSpecial with xlPasteFormulas for more complicated formulas.

Cells.SpecialCells(xlCellTypeConstants, 3).PasteSpecial Paste:=xlPasteFormulas