Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro for update the sheet automatically from master sheet

    Hi Everyone,
    I am newbie to macros. I have to build a macro that will update the worksheet (Sheet1) from the master sheet (Sheet2). There is a master sheet that contains 1000's of records each is having unique ID. This master sheet is getting updated on daily basis. I have a sheet in the same workbook that contains some data points from master sheet. I have to update the Sheet1 for the unique ID's matched in Sheet2. The below is the column headers for Sheet1 & Sheet2;

    Sheet2
    ID Name Activity email Mobile Start Date End Date

    Sheet1
    ID Name Start Date End Date

    Is there any macro for this that will update the data automatically. Any help is highly appreciated. Please let me know for any clarifications. Thank you very much.

    Best Regards,
    Abhishek

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Abhishek

    ..welcome to the Lounge as a new poster.

    ..it would be easier for us to help you if you could attach a sample workbook, with some dummy data.
    We could then put code into the workbook and post it back.

    zeddy

  3. #3
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Zeddy for your quick response. Please find attached the spreadsheet with dummy data. I need to update the Sheet1 if there is any change in data in Sheet2 for matched Unique ID's. Awaiting your response.

    Thank you very much.

    Best Regards,
    Abhishek
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Abhishek

    You can 'update' the details on [sheet1] from the master data on [sheet2] using formulas, rather than macros.

    In the attached file, I have used a formula in column [B] on [sheet1] that find out what row number the ID in the adjacent cell (in column [A] ) matches the same ID on [sheet2]. If the ID cannot be found on the master sheet, the formula will return a 'not found' entry for the cell.
    This formula column [B] on [sheet1] could be hidden.

    If we know the matching row number for the unique ID, we can use an INDEX formula to 'retrieve' the matching entry from any column on [sheet2].
    Have a look at the formulas I've used for retrieving the data.
    Using the INDEX formula in this way is much more efficient than using any VLOOKUP formula.

    I have shown how you can get data from any column you want.
    Any questions, just ask.

    zeddy
    Attached Files Attached Files

Posting Permissions

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