VBA code(?) to take out specific formulas in all worksheets

[thomas.mapua]

Hi,

I use a specific formula in Excel to pull data out of our SAP database =getctdata(parameters etc...). I need a VBA code to copy all #'s that the formula pulls out from our database and paste as values on all worksheet within the file.

The reason I need to take the formula out is that in order for =getctdata formula to work, you will need to be within the SAP database, once you are NOT logged in, it will show #NAME? on all cells that have that formula. Unfortunately, the SAP database is very slow and I can't work efficiently in Excel when I am logged in. My quick fix on this is to copy and paste values on all cells, but when I use that I take out all other formulas that I have (like sum, count formulas etc...)and I cannot make what if analysis readily on my worksheets.

Anyone able to help me figure out a solution? I'm assuming this will be a VBA code that loops throughout all the cells in each worksheet looking for =getctdata cells and copies and paste them as values.

Thanks all!

[rylo]

Hi

Try

Please Login or Register  to view this content.

rylo

[thomas.mapua]

Have been so busy and lost track of this project. Thanks for the reply rylo.
The macro you provided just scrolled around my worksheet and didn't do anything.

I might have to rephrase what I was looking for.

1) Select all cells with value "getctdata" on P&L by Month worksheet only (not all sheets in the workbook)
2) Once all cells are highlighted do a copy and paste as values

I tried using the Find feature then find all and select all however I get an error that says "That command cannot be used on multiple selections" whenever I copy it.

Thanks for the help!