Creating a macro that will copy and paste an Index formula onto another worksheet

  • I have two formulas that I am trying to create a macro for that will copy the formula from one sheet and paste the formula onto another sheet. The formulas I am using are:


    Retrieve Model # =IFERROR(INDEX(AllParts.csv!$F:$F,MATCH($B3,AllParts.csv!$E:$E,0)),"")
    Retrieve Description =IFERROR(INDEX(AllParts.csv!$H:$H,MATCH($B3,AllParts.csv!$E:$E,0)),"")


    The formula references an external workbook (AllParts.csv), but the formulas are located on a separate sheet (Formulas) in the "Main" workbook. The "Main" workbook has the following:


    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Main

    [/td]


    [td]

    Main

    [/td]


    [td]

    Main

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Part #

    [/td]


    [td]

    Model #

    [/td]


    [td]

    Description

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    101

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    102

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    103

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    104

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    105

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    106

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]

    107

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    I tried recording a macro that would copy and paste the formula into cell B3, it changes "B3" to "B5"

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    "copy and paste the formula into cell B3, it changes "B3" to "B5""
    Where are the original formulas ?
    Can you attached a short sample of your data, it will help to understand

    Triumph without peril brings no glory: Just try

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    You want to copy the first row and then paste it until the last row ?
    BTW instead of the formulas used in the file attached, you could use a VLOOKUP ...!

    Triumph without peril brings no glory: Just try

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    Yes, unless there is an easier way to fill in both B and C columns with a macro. What is the advantage or differences between VLOOKUP and INDEX

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    The VLOOKUP is more simple in the use, but the value used as reference must be in the first column.
    It seems that in the file sent you use column "A" for the reference , is it?

    Triumph without peril brings no glory: Just try

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    yes, column a is the part number that I reference on both workbooks

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    Try

    Triumph without peril brings no glory: Just try

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    If not too much to ask, can you explain the code and how it works? I am trying to learn code and would really like to pick apart different codes and mix and match them with others

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    Also, how does this reference my other workbook to look up the values? I guess I am used to seeing the name of the workbook in the code, itself.

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    If you do not have any knowledge about VBA it will take time.
    What you could do to start: go step by step in the code using F8 key and see the effect in excel tab

    Triumph without peril brings no glory: Just try

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    thank you, I really appreciate your help

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    The macro just Copy the formula already written in the first row and paste it down until the last row

    Triumph without peril brings no glory: Just try

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    Sorry my fault, guess I should have explained myself better. I posted the formula to show what I was using. Basically I want the macro to do the formula, the spaces for serial number and description would be blank, but filled in once the macro ran

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    Is it what you want

  • Re: Creating a macro that will copy and paste an Index formula onto another worksheet


    awesome work, thank you. I will make some minor mods, but this works perfectly

Participate now!

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