Macro: Identify particular row and paste from specific point

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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!


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

    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!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!