+ Reply to Thread
Results 1 to 5 of 5

Copy data from another worksheet if it doesn't exist in current worksheet

  1. #1
    Registered User
    Join Date
    02-05-2018
    Location
    India
    MS-Off Ver
    2013
    Posts
    11

    Copy data from another worksheet if it doesn't exist in current worksheet

    I have 2 worksheets in my workbook, one is named as "Data" and another one is named as "Account Definition Mapping", the Data worksheet consists of 3 columns .i.e. Entity Code (Column A), Account Number (Column B) and Account Definition (Column C) and Account Definition Mapping consists of Entity code and Account Definition column in 2 sets (.i.e. Column A & B [Account definition model 1] and D & E [Account Definition Model 2]), first set is called Account Definition Model 1 and Second set is called Account Definition Model 2.

    Now I want my macro to check the Data Worksheet Column A, B and C in combination and if for a particular account the entity code and account definition doesn't exist then copy the same from Account Definition mapping worksheet and insert those rows for that account in Data worksheet. The issue over here is we don't have the account number column in the account Definition mapping worksheet so we can't simply compare both directly one to one, Also Account definition mapping model can be chosen by user by selecting the same from Data sheet Cell J2, based on which macro should either check from column A and B if user selects Account Definition Model 1 but If he selects Account Definition Model 2 then macro should check the existence of data in column D and E of Account Definition Mapping worksheet.

    Below is the code which I have got which ideally check based on all fields comparing one to one between both worksheets but as mentioned earlier the problem is that we don't have the account number field in the Account Definition Worksheet so it's comparing the 3 columns data .i.e. Entity Code, Account Number and Account Definition combination from the data worksheet against 2 columns .i.e. Entity Code and Account Defintion in Account Definition mapping to see the entity code and account definition combination listed in Account Definition mapping field exists in the Data worksheet for all the accounts in Data worksheet and if not then add the same and highlight in yellow. Attached is the workbook.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by abhay_547; 04-07-2019 at 09:32 AM.

  2. #2
    Registered User
    Join Date
    02-05-2018
    Location
    India
    MS-Off Ver
    2013
    Posts
    11

    Re: Copy data from another worksheet if it doesn't exist in current worksheet

    Any luck ? did anyone get the chance to look into the above code.

  3. #3
    Registered User
    Join Date
    04-10-2019
    Location
    Kansas City, MO
    MS-Off Ver
    Microsoft Office 2016
    Posts
    3

    Re: Copy data from another worksheet if it doesn't exist in current worksheet

    Based on your original Account Definition Template.xlsm, it appears that Data!B:B contains 9 unique account numbers, yet you only have 2 account definition models on Account Definition Mapping. Questions:

    1. Are you looking to evaluate all 9 unique account numbers in a single click?
    2. If you are wanting the script to evaluate for previously existing/non-existing data (to highlight updates in yellow), given the lack of account numbers on your Account Definition Mapping sheet, what is the logic behind which Account Definition Model should be used to compare to each account number for pre-existing data?

  4. #4
    Registered User
    Join Date
    02-05-2018
    Location
    India
    MS-Off Ver
    2013
    Posts
    11

    Re: Copy data from another worksheet if it doesn't exist in current worksheet

    Quote Originally Posted by Kristen Yamagata View Post
    Based on your original Account Definition Template.xlsm, it appears that Data!B:B contains 9 unique account numbers, yet you only have 2 account definition models on Account Definition Mapping. Questions:

    1. Are you looking to evaluate all 9 unique account numbers in a single click?
    2. If you are wanting the script to evaluate for previously existing/non-existing data (to highlight updates in yellow), given the lack of account numbers on your Account Definition Mapping sheet, what is the logic behind which Account Definition Model should be used to compare to each account number for pre-existing data?

    As mentioned in my initial thread account number is not available in mapping but we have other 2 fields .i.e. entity code and account definition which are common in data worksheet and account definition mapping worksheet and that's the trouble I can't include the account column in account definition mapping worksheet and hence I want to check if for each account listed in data worksheet do we have the entity code and account definition combination available in data worksheet and if not then add the same.

  5. #5
    Registered User
    Join Date
    02-05-2018
    Location
    India
    MS-Off Ver
    2013
    Posts
    11

    Re: Copy data from another worksheet if it doesn't exist in current worksheet

    Any luck ? did anyone get the chance to look into the above code.

+ 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] If worksheet name doesn't exist from list then create using template
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-18-2017, 02:37 PM
  2. [SOLVED] Macro to delete worksheet/tab if specific text doesn't exist
    By arpanpanchal in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-06-2016, 01:00 AM
  3. Replies: 20
    Last Post: 02-11-2015, 04:42 PM
  4. does formula exist to copy data to another worksheet in real time?
    By belinda_235 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-08-2015, 06:16 AM
  5. How to delete a worksheet if it doesn't exist in a list
    By iamrickdeans in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2013, 09:46 AM
  6. Replies: 4
    Last Post: 06-18-2006, 01:10 PM
  7. search for worksheet, insert new if doesn't exist
    By cereldine in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2006, 12:15 PM

Tags for this Thread

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