Another Excel query!
Thread Starter
Join Date: Feb 2006
Location: Here and there
Posts: 216
Likes: 0
Received 0 Likes
on
0 Posts
Another Excel query!
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
Spoon PPRuNerist & Mad Inistrator
Sounds like you need to start looking at VBA!
You might be better off looking at a site specifically catering for computing - e.g. eggheadcafe
SD
You might be better off looking at a site specifically catering for computing - e.g. eggheadcafe
SD
Thread Starter
Join Date: Feb 2006
Location: Here and there
Posts: 216
Likes: 0
Received 0 Likes
on
0 Posts
Well, I managed to solve the problem without even realising initially! SD, i'm already using 6 VBA macros in this sheet to make all of the calculations work properly!
One last issue: I have VBA macros accessing data on worksheets that I want to hide. However, whenever I hide the worksheets and attempt to run the macros they display a run time error, as they cannot find the hidden sheets. Is there any parameter that you can enter in to the macro to tell it that the sheet it is looking for is hidden, and any way of allowing it to copy the data without unhiding the sheet?
One last issue: I have VBA macros accessing data on worksheets that I want to hide. However, whenever I hide the worksheets and attempt to run the macros they display a run time error, as they cannot find the hidden sheets. Is there any parameter that you can enter in to the macro to tell it that the sheet it is looking for is hidden, and any way of allowing it to copy the data without unhiding the sheet?
