Macro to F2-F9 a selection of cells

[cbjfan2009]

Good morning,
I'm trying to program a macro that: 1) extracts a specific sheet from an indexed workbook; 2) converts all of the formula-derived cells into their absolute values; 3) opens up a save-as prompt.

My workbook has index formula to auto-populate multiple spreadsheets. When I try to extract one of the workbooks to save separately (to send out-of-company), the problem is that the index-derived cells stay linked to the original document. I can manually select every formula-derived cell, hit F2 to edit, then F9 to calculate and replace the formula with the value, but that's time consuming. Can I program a macro to do all of that? I tried, but I failed.

The worksheet I'd like to extract is named 'Silliker SARF', or 'FSNS SARF' (two separate workbooks that use the same system and I'll be adding this macro to both).
Cells to convert from formula to value:
F19, I24, J24, K24, L24, B33, D33, I33, J33, K33, L33, I34, J34, K34, L34 and maybe adding more later...which I can do once I see the coding pattern.

Any thoughts?
Thanks to one and all!

[Norie]

You could use code to do a copy then paste special values.

Please Login or Register  to view this content.

Or just this.

Please Login or Register  to view this content.

[cbjfan2009]

Do I put this in the Alt-F11 VBA prompt, or do I program a macro with
Range("F19").Copy
Range("F19").PasteSpecial xlPasteValues

Again, the most important part of this process is to isolate that single spreadsheet into a new file, which I've done with Macro...but I can't figure out the coversion of Formula to value for all cells inside that Macro.
Thank you.

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[cbjfan2009]

John Davis, I'm sorry, but I'm not sure what to do with that. Where do I insert that code? Into the VBA under the "ThisWorkbook" heading? If yes, how do I execute this? I don't want it running by itself. I'd like this procedure to only run on the copied file.

[BadlySpelledBuoy]

I tend to use something along the lines of the code below.

Change Sheet1 to the sheet name you wish to copy to a new workbook.

Put this code in a regular module and execute it via a button.

Please Login or Register  to view this content.

Does that do what you need?

BSB.

[JOHN H. DAVIS]

No goes in a Standard Module. If you do not have one, then in the VBE toolbars>Insert>Module. Paste the code there.

[cbjfan2009]

Thanks BSB and John H Davis. This is working. Odd 'glitch', when the Save-As prompt opens, I type in a name and click save, but it doesn't save. I have to go back in and click save-as manually. Not the end of the world...I'm sure my co-worker can figure that out!
Thanks a ton, everyone!

[BadlySpelledBuoy]

Amended the code above to deal with the SaveAs properly.

Hope that helps.

BSB