Copy and paste values for all cells that begin with a certain string

Hey guys, hope you can help me again. I have some add-ins in Excel that use custom functions to pull data for a larger document. The add-in is not available on all computers that will be using this spreadsheet, so I would like to find all the cells that are dependent on these custom functions (through a COM Add In) and copy and paste values.

So the cells that need copied and pasted begin with (for example) "=+XYZ(" and "=+XYZTWO(" and they are found in numerous cells across many worksheets (i.e. they're not neatly co-located on one sheet).

I'm thinking some VBA will solve this. I appreciate any help you can give. Thanks.

What type of values do you wish to paste into the cells? What datatype?

Are all workbooks that you want to search open when you run the search?

If not, are they in the same directory as the workbook that runs the code?

If not, are they all in the same directory, different than the workbook that runs the code?

I would try this:

Please Login or Register  to view this content.

...please let me know how that worked.

Hi joe, thanks for the reply. All the data returned are numbers, and I would like to paste everything as a number. Formatting (some are percents, some are dollar values) is not important as long as the number value remains intact.

Also, there is only one workbook. One file. There are multiple worksheets. I didn't get a chance to try your code - thanks for posting it. Will it still be appropriate for one workbook across multiple sheets? Thanks a ton!

It will work because Workbooks is a collection of open workbooks, so it should just loop through one workbook if it's the only one open.

Please Login or Register  to view this content.

I changed the code above to reference the workbook the code is written in. That way, if you have other workbooks open, the code will work if the code is stored in the workbook you want to search. I also changed the datatype of "Variant1" and "Variant2" to a Double, which allows you to use just about any number you want. I would change the Variant1 & Variant2 names throughout the code to something more meaningful to you.

Got it, thank you very much. I am not able to test it now but will tomorrow and I'll let you know how it goes. Thanks!