+ Reply to Thread
Results 1 to 2 of 2

Macro to Copy & Paste when worksheet names matches

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2007

    Exclamation Macro to Copy & Paste when worksheet names matches

    Hello Experts,

    A rookie in VBA programming.

    I have a workbook with multiple spreadsheets with unique names. In this workbook, I have a master sheet which lists the names of all worksheets in column A.

    I need to have a macro to have this condition:
    Look for value in column A (master sheet) and compare with all the worksheets. If a match found, copy the value from coulmn B & C from the matched worksheet and paste it into column B & C of the master sheet.

    for eg:
    column A4: Test 4
    Worksheet name = Test 4

    Match? Yes
    Copy values from column B & C and paste into Master sheet starting B4 & C4

    Hope my questions makes sense!

    Thanks for all your help in advance

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365

    Re: Macro to Copy & Paste when worksheet names matches

    Copy what values from sheet Test 4?

    If the value want is cell A1 on that sheet, then put this formula in B4 and copy down:

    =IF(ISREF(INDIRECT("'" & A4 & "'!A1")), INDIRECT("'" & A4 & "'!A1"), "")

    Just edit the part in red to pull back that specific cell value on the mathching sheet. You don't have to edit the first one.
    Last edited by JBeaucaire; 04-30-2012 at 06:57 PM.
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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