processing ranges in a macro
Posted by David on January 08, 2002 9:56 AM
I distribute financial statements to people outside my company, and I have to change the formulas in some ranges to values before I send them out. For example, I may have the following labels in Col A with their values in Col B:
Where the formula for Total Expenses in Col B is the sum of the three rows above. Labor, Travel and, Adim are formulas would be linked to supporting calculations on other worksheets.
I need to change the formulas for Labor, Travel, & Admin to values, but leave the sum formula for Total Expenses alone. I have a number of different ranges to convert to values, and they are not uniform sizes.
I came up with the following macro:
Sub PasteValue(MyFile, MySheet, MyRange)
Public Sub TestIt()
Call PasteValue("TestPaste.xls", "Sheet1", "TestArea")
Call PasteValue("TestPaste.xls", "Sheet1", "TestArea2")
Call PasteValue("TestPaste.xls", "Sheet1", "TestArea25")
Is there a cleaner way to do this? Is there an easier way to process a list of ranges than having a long list of "call" statements?
Thanks in advance from a novice macro writer.