+ Reply to Thread
Results 1 to 19 of 19

Copy Data from One to Another based on Reference Number

  1. #1
    Forum Contributor
    Join Date
    04-14-2017
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    148

    Copy Data from One to Another based on Reference Number

    Good Day,

    My query is from a previous thread that has been resolved and many thanks to John Tropley for the solution.

    My new query based on John's solution which works, SInce I have amended the spreadsheet to include more columns and adapt the code and when I run the macro from John's solution, I am getting the following error "
    Please Login or Register  to view this content.
    - Run-time error '9': Subscript out of range

    Essentially what is meant to happen is that any changes made on Sheet FARJUL the macro is meant to update sheet FARJUN

    The code is as follows:

    Please Login or Register  to view this content.
    I have attached sample of file

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy Data from One to Another based on Reference Number

    I'm not sure why you check each individual column within each row...Are their not Unique Column Qualifiers that are the same in both sheets...
    Surely if you make a change in any column in FARJUL then that whole matching row in FARJUN can just be updated with the row from FARJUL
    Or am I missing something...Or why not put a Worksheet_Change event in place that runs once on change...then you would not need to loop through both Sheets...

    Something like this...Code in sheets("Farjul").Sheet module
    Please Login or Register  to view this content.
    I notice you posted this query on your initial thread here...Why not just carry on with that thread...
    Last edited by sintek; 09-02-2021 at 04:44 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Contributor
    Join Date
    04-14-2017
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    148

    Re: Copy Data from One to Another based on Reference Number

    HI Sintek

    Reason for new thread I was advised to do so ...

    I have tried your code by inserting into module for Sheet FARJUL, when i make a change , its not making the change in Sheet FARJUN

    Thanks

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy Data from One to Another based on Reference Number

    You are doing something wrong...as it works with your sample file...Make a change in any Column of any row within your data and see that change is updated...In other sheet
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Copy Data from One to Another based on Reference Number

    I'm not sure why you check each individual column within each row...Are their not Unique Column Qualifiers that are the same in both sheets..
    You are correct here: I was playing "safe" in not assuming the references are the same. But it makes little difference in terms of run time!

    And yes: why not Worksheet_change event!

  6. #6
    Forum Contributor
    Join Date
    04-14-2017
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    148

    Re: Copy Data from One to Another based on Reference Number

    HI Sintek

    You were right I was doing something wrong , work absolutely brilliant,
    Thanks a million .... Another question I have is can I adapt the code to loop through FARJUN and update any changed rows from FARJUL?
    Thanks

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy Data from One to Another based on Reference Number

    @ JohnTopley...Was in no way a negative reflection on your excellent code...Was just wondering why OP wanted to check all columns per row for change...

    @ farhadj
    just place the code in the other sheet module and change names around...

  8. #8
    Forum Contributor
    Join Date
    04-14-2017
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    148

    Re: Copy Data from One to Another based on Reference Number

    HI Sinek

    I tried interchanging File code into the other Sheet module as suggested. I am not getting a loop on the sheet , I have attached the sheet
    You will notice on FARJUL , i changed CELLS C9:C13 and when i close and open the Sheet , the changes have not been made
    Thanks
    Attached Files Attached Files

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy Data from One to Another based on Reference Number

    That's because you removed the code in that sheet Farjul Module
    Both sheets need the code inserted or one can put a single code in This Workbook.Module...
    But that is a combination code that will require a select statement...
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    04-14-2017
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    148

    Re: Copy Data from One to Another based on Reference Number

    HI Sintek

    Thanks for your assistance, code works as required. Just one more question, These sheets has about 7000 rows of data, and I have already copied and pasted about 2000 rows. How can use your code to update the FARJUN sheet without having to copy and paste.
    Thanks

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy Data from One to Another based on Reference Number

    As a once off to update current changes prior to installing my code?

  12. #12
    Forum Contributor
    Join Date
    04-14-2017
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    148

    Re: Copy Data from One to Another based on Reference Number

    Yes , appreciate your assistance

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy Data from One to Another based on Reference Number

    So why not just make use of John's original code that works...
    If you cannot adapt then start by explaining in detail step by step what you want this code to do...Refer to sheets and ranges and explain a reason for a specific result | output...
    Last edited by sintek; 09-07-2021 at 03:40 AM.

  14. #14
    Forum Contributor
    Join Date
    04-14-2017
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    148

    Re: Copy Data from One to Another based on Reference Number

    HI

    I have tried adapting the code due to adding more columns. At this point I having issues with the code.
    I am getting an error "Run-time error '9' Subscript out of range". I have attached the sheet with additional columns and modified code.
    I also noticed the John's solution has a Log sheet which is not a requirement




    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy Data from One to Another based on Reference Number

    If it is merely referencing Ref No's and checking those only for changes then this will work...
    Please Login or Register  to view this content.
    If not, I suggest you go back to your original thread - as that worked - and ask for an amendment...
    Or perhaps John...The Author of code will hop on and amend...
    I personally do not like making changes to other Members' code...
    Last edited by sintek; 09-07-2021 at 05:57 AM.

  16. #16
    Forum Contributor
    Join Date
    04-14-2017
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    148

    Re: Copy Data from One to Another based on Reference Number

    Hi Sintek

    Thanks for the code , i tested on small number of records and works well. However when i use the code with all records , Excel hangs on me , not sure why. Do you think filtering records will make it easier or updating specific columns? There are 7229 rows and 49 columns of data.. Only columns that need to be updated are columns J,K,L,M,N,AE

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy Data from One to Another based on Reference Number

    With 7229 rows there is no reason for Excel to hang...Upload file and sure someone can offer a faster solution...
    It is for a once off anyway...The Sheet_change code will be used for future...

  18. #18
    Forum Contributor
    Join Date
    04-14-2017
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    148

    Re: Copy Data from One to Another based on Reference Number

    Good Day

    @Sintek @ John Topley my sincere apologies for the late reply. Just wanted to thank both of you for the solutions provided.
    Once again thanks a million

    Regards

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copy Data from One to Another based on Reference Number

    Pleasure...
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED
    Thanks for rep +
    Last edited by sintek; 09-28-2021 at 03:11 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Copy/paste data based on cell reference (indirect)
    By dunder in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-18-2019, 12:57 PM
  2. Replies: 2
    Last Post: 10-13-2015, 05:05 PM
  3. Replies: 0
    Last Post: 09-28-2015, 06:32 AM
  4. VBA to copy data with each change in reference number, save & loop
    By Marijke in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2015, 10:23 AM
  5. Copy data over based on reference and named range
    By jedemeyer1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2014, 07:44 AM
  6. [SOLVED] Copy data from One Spreadsheet to another based on a particular reference
    By filbert83 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-20-2012, 02:38 AM
  7. Replies: 2
    Last Post: 05-30-2012, 05:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1