Macro: Identify particular row and paste from specific point

Hi!

I have inherited a couple of large data tables ('Original' and 'Revised') which I am working to streamline as best as possible. Part of this is automating the process of modelling and applying changes. I have created a worksheet 'Changes' which pulls data from 'Original' using a unique row ID & vlookups and then allows the user to make or model required changes. I then wish for the changes to be able to be applied to the 'Revised' worksheet.

I am trying to write a macro that will identify the correct row in the 'Revised' data table, using the unique row ID (Column A). And then paste an array (1 row x 78 columns) into that row, starting at column K. (overwriting anything that was already there). Does that make sense?

I recorded the macro by copying the required ID from 'Changes', finding it in 'Revised' (via Ctrl+F), then manually moving the selection right by 9 cells to identify the required cell. Unfortunately this didn't work and the eventual cell reference was hard-coded into the macro.

Can anybody give me any advice for how alter my macro (or completely re-write in VBA) so that it can look-up and select the required row and move the selection to row K ready for the data to be pasted?

Please ask for clarification if I have been unclear on anything (or all!). Any advice would be greatly appreciated!

JB

Re: Macro: Identify particular row and paste from specific point

Quote

Quote from S O;731129

Hi JB, welcome to ozgrid

Sounds like you simply need a combination of Find and Range.Resize() - can you upload an example of your data?

Hi S O & Thank you,

Thanks for getting back to me. Unfortunately when it comes to Macros/VBA, my experience is mainly of manually recording them, and then making minor alterations in VBA! Could you give me an example of how the Find & Range.Resize operations would work in this scenario?

Unfortunately due to the actual version holding sensitive data, I cannot upload the original. However I have created and attached a very basic equivalent which I hope will demonstrate what I'm after.

Please let me know if it's still unclear or if you need any other info to assist.

Thank you!

JB