Thanks to SolnaJeff I have the following macro that I use for a worksheet:
The purpose of it is to auto-fill the formulas on one table according to the raw data I copy/paste to another table (in the same worksheet).
So I went on to assign this macro to a button so that I do the following:
a) Create my data table by copying pasting all the raw data I need
b) Click on my button to have my other table get autofilled with the formulas I have put in the first row (the macro auto-fills the rows under the one row that contains all my formulas).
All this is happening into one worksheet. Now my next problem is that I have far too many rows which means equally far too many cells autofilled with formulas. Unfortunately Excel 2002 that I am using has the defect of only 64 RAM that can handle within a document. This results to error messages and corrupted files after a few dozen thousand rows full of formulas.
The way to solve this problem (as I have figured it out) is the following:
Suppose that my original table with the first row with the formulas is at K5-R5 and I have rows from K6-R10000 autofilled with the macro. Then I select the cells from K6-R10000, copy->paste special->paste values. This way I replace all the autofilled cells with their hardcoded values instead of formulas, which clears the available memory for excel to use it in the next worksheet.
Is there any way to do this with the same macro (or an extra one?). My problem is that the number of rows that are autofilled is not specific and can vary dramatically. So I would want an addition to the macro above that after it has autofilled the rows/cells according to the table of raw data, it then proceeds on doing a copy->paste special->paste value to all the cells it has autofilled.
Is that possible?
Also, another small bit, is it possible that when the original formula autofills the rows below it does NOT copy the visual format of the row above (borders, colors etc) ?
If I am not making sense please let me know and I will try to become more clear.
Bookmarks