Hi All,
Looking for a bit of guidance re: the above.
I have inherited rather ungainly couple of data tables (let's call them 'Original' & 'Revised') and am working on an easier way for changes to be made or modeled On a separate worksheet ('Changes') I have done all the leg-work in terms of modifying the 'Original' data, the final step is to copy the output (an array of cells 1 row x 78 columns) into the appropriate location on the 'Revised' data table.
I would like to do this with a macro, attached to an 'Update' button for example, so changes and tweaks can be made before any data is overwritten.
I tried to record a macro to copy the row ID from the 'Changes' worksheet, then move to the 'Revised' sheet and use the Find function to identify the correct row, then used the Right key to navigate to the correct column (K). Now I have the correct destination cell highlighted, back to 'Changes' and copied the data array. Finally, switch back to 'Revised' and Paste Values. Does that make sense?
Unfortunately when I look at the macro in VBA, the Find operation has recorded the absolute value of the first copied cell when I recorded the macro, rather than picking up any value that particular cell may contain. Furthermore, my attempts to select the relevant column appear to have hard-coded a cell reference into the macro.
So as it stands, my macro will work... except it is always going to paste the data onto one particular row in my Data Table! How can I amend this so it will use the correct row ID and paste the data into the relevant cell reference?
A couple of things to note:
The array will always be pasted into column K, however the row could be anything between 2 and 150.
The array will always be the same size and copied from a set location on the 'Changes' worksheet
The array must be pasted as values.
Apologies if I have not made sense here, any advice would be appreciated!
Thank you,
JB
Looking for a bit of guidance re: the above.
I have inherited rather ungainly couple of data tables (let's call them 'Original' & 'Revised') and am working on an easier way for changes to be made or modeled On a separate worksheet ('Changes') I have done all the leg-work in terms of modifying the 'Original' data, the final step is to copy the output (an array of cells 1 row x 78 columns) into the appropriate location on the 'Revised' data table.
I would like to do this with a macro, attached to an 'Update' button for example, so changes and tweaks can be made before any data is overwritten.
I tried to record a macro to copy the row ID from the 'Changes' worksheet, then move to the 'Revised' sheet and use the Find function to identify the correct row, then used the Right key to navigate to the correct column (K). Now I have the correct destination cell highlighted, back to 'Changes' and copied the data array. Finally, switch back to 'Revised' and Paste Values. Does that make sense?
Unfortunately when I look at the macro in VBA, the Find operation has recorded the absolute value of the first copied cell when I recorded the macro, rather than picking up any value that particular cell may contain. Furthermore, my attempts to select the relevant column appear to have hard-coded a cell reference into the macro.
So as it stands, my macro will work... except it is always going to paste the data onto one particular row in my Data Table! How can I amend this so it will use the correct row ID and paste the data into the relevant cell reference?
A couple of things to note:
The array will always be pasted into column K, however the row could be anything between 2 and 150.
The array will always be the same size and copied from a set location on the 'Changes' worksheet
The array must be pasted as values.
Apologies if I have not made sense here, any advice would be appreciated!
Thank you,
JB
