Oxeagle
6th Nov 2006, 15:47
Once again, I am calling on my fellow pruners for help with Excel :)
Here's the situation: I want to copy data from spreadsheet one and paste it into a seperate spreadsheet, spreadsheet two. Sounds simple enough? Now here's the twist... This data transfer is meant as a way of updating spreadsheet two. This in itself is not a problem. The data being copied from spreadsheet one to spreadsheet two is pasted in using the 'paste special - values' function, as they are in fact formulae but I just want the values. New data is always being added to spreadsheet one, so the cell numbers are never the same.
However, the last eight rows of data to be copied are different formulae, as the data that these cells refer to is meant to be manipulated by the user, and I want to paste these into spreadsheet two using the 'paste special - formulae' function. I already have a working macro that copies all of the data from spreadsheet one to spreadsheet two, including the eight rows of user defined data, but it only copies the values. The obvious solution would be to reprogram the macro to copy these eight rows using the paste formulae function, and the rest of the data using the paste values function, but the last eight rows of data never have the same row numbers, because as soon as another entry is created it pushes them down a row. Also, the macro is extremely basic; all it does is copy a large block of cells regardless of what is in them from spreadsheet one and
pastes them into spreadsheet two.
So, because of this I cannot program the macro copy the last eight rows seperately. Anyone have any ideas? I'm hoping there might be a formula that compares the formulae of a set of cells and can differentiate between them (i.e. it can tell the difference between VLOOKUP and IF formulae) so that I can program the macro to paste cells with one type of formulae in them as formulaes, and other cells with another type of formulae as values.
Any ideas anyone?
Cheers,
Ox :)
Here's the situation: I want to copy data from spreadsheet one and paste it into a seperate spreadsheet, spreadsheet two. Sounds simple enough? Now here's the twist... This data transfer is meant as a way of updating spreadsheet two. This in itself is not a problem. The data being copied from spreadsheet one to spreadsheet two is pasted in using the 'paste special - values' function, as they are in fact formulae but I just want the values. New data is always being added to spreadsheet one, so the cell numbers are never the same.
However, the last eight rows of data to be copied are different formulae, as the data that these cells refer to is meant to be manipulated by the user, and I want to paste these into spreadsheet two using the 'paste special - formulae' function. I already have a working macro that copies all of the data from spreadsheet one to spreadsheet two, including the eight rows of user defined data, but it only copies the values. The obvious solution would be to reprogram the macro to copy these eight rows using the paste formulae function, and the rest of the data using the paste values function, but the last eight rows of data never have the same row numbers, because as soon as another entry is created it pushes them down a row. Also, the macro is extremely basic; all it does is copy a large block of cells regardless of what is in them from spreadsheet one and
pastes them into spreadsheet two.
So, because of this I cannot program the macro copy the last eight rows seperately. Anyone have any ideas? I'm hoping there might be a formula that compares the formulae of a set of cells and can differentiate between them (i.e. it can tell the difference between VLOOKUP and IF formulae) so that I can program the macro to paste cells with one type of formulae in them as formulaes, and other cells with another type of formulae as values.
Any ideas anyone?
Cheers,
Ox :)