VBA toTransfer Data from one workbook to another workbook.

  • To all,


    I am lookin for a macro to transfer data that we have on one workbook to another workbook. The purpose is to take our quotes that we generate to the customer and transfer this to a master price file that the customer then uses to upload into their purchasing system. The file we will be transferring from will be variable but the file we are transferring to will be static.


    ------------------------------
    I'd like the macro to do the following things if possible:


    1) Find and insert the total from WB1 into WB2 using the KC #### in cell A2 and the Color starting from Cell E6
    2) Open the workbook we will be transferring to (Workbook2) prior to transferring the data. The file is always in the same folder and the name never changes. (Optional but helpful)
    3) If the macro can't find a value for a particular color, skip it an go to the next column. I need it to transfer all of the data and not get hung up if it can't find a place to dump a value from a particular column. All of the information in columns A-M come from the customer and we can't modify that. If the customer neglected to give us their part# for a color, there won't be an entry place for that particular color and total from workbook1. We want to skip it and if the customer gives us their part# in the future, we can rerun the macro on the quote and pull in the number then.


    -------------------------------
    The data I want to transfer from Workbook1 is in the following format:


    [TABLE="class: cms_table_cms_table_grid"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [td]

    F

    [/td]


    [td]

    G

    [/td]


    [td]

    H

    [/td]


    [td]

    I

    [/td]


    [td]

    J

    [/td]


    [td]

    K

    [/td]


    [td]

    L

    [/td]


    [td]

    M

    [/td]


    [td]

    N

    [/td]


    [td]

    O

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Builder Name

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    KC 3125 Subdivision name

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Room Type

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    Date

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    DESCRIPTION

    [/td]


    [td]

    QTY

    [/td]


    [td]

    MODIFICATIONS

    [/td]


    [td]

    OPTIONS

    [/td]


    [td]

    COLOR 1

    [/td]


    [td]

    COLOR 2

    [/td]


    [td]

    COLOR 3

    [/td]


    [td]

    COLOR 4

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    ASDF

    [/td]


    [td]

    #

    [/td]


    [td]

    ASDF,ASDF

    [/td]


    [td][/td]


    [td]

    $$

    [/td]


    [td]

    $

    [/td]


    [td]

    $$$

    [/td]


    [td]

    $

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    ASDF

    [/td]


    [td]

    #

    [/td]


    [td]

    FDSA

    [/td]


    [td][/td]


    [td]

    $

    [/td]


    [td]

    $$

    [/td]


    [td]

    $

    [/td]


    [td]

    $$

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    ASDF

    [/td]


    [td]

    #

    [/td]


    [td]

    FDSA

    [/td]


    [td][/td]


    [td]

    $$

    [/td]


    [td]

    $$

    [/td]


    [td]

    $

    [/td]


    [td]

    $$$

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]

    ASDF

    [/td]


    [td]

    #

    [/td]


    [td][/td]


    [td][/td]


    [td]

    $$

    [/td]


    [td]

    $

    [/td]


    [td]

    $$$

    [/td]


    [td]

    $

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td]

    ASDF

    [/td]


    [td]

    #

    [/td]


    [td]

    ASDF

    [/td]


    [td][/td]


    [td]

    $

    [/td]


    [td]

    $$$

    [/td]


    [td]

    $$

    [/td]


    [td]

    $$

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td]

    Total

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Total of Column

    [/td]


    [td]

    Total of Column

    [/td]


    [td]

    Total of Column

    [/td]


    [td]

    Total of Column

    [/td]


    [/tr]


    [/TABLE]


    Please note that the number of rows is variable (The totals will not always be in row 14) but the number of columns is static (We only offer so many colors to this customer and it's not our entire line)


    -----------------------------------------------------------------


    On the workbook I need to transfer to (Workbook 2), in column P is the following format (DWG#KC-3125) *Note, the KC can also be a KV*
    In column Q is the Color. In Column O I want to put the total for the color.


    [TABLE="class: cms_table_grid"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [td]

    F

    [/td]


    [td]

    G

    [/td]


    [td]

    H

    [/td]


    [td]

    I

    [/td]


    [td]

    J

    [/td]


    [td]

    K

    [/td]


    [td]

    L

    [/td]


    [td]

    M

    [/td]


    [td]

    N

    [/td]


    [td]

    O

    [/td]


    [td]

    P

    [/td]


    [td]

    Q

    [/td]


    [td]

    R

    [/td]


    [td]

    S

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    (Need Total from E14 on Other Sheet)

    [/td]


    [td]

    (DWG KC-3125)

    [/td]


    [td]

    Color 1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    (Need Total from F14 on Other Sheet)

    [/td]


    [td]

    (DWG KC-3125)

    [/td]


    [td]

    Color 2

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    (Need Total from G14 on Other Sheet)

    [/td]


    [td]

    (DWG KC-3125)

    [/td]


    [td]

    Color 3

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    (Need Total from H14 on Other Sheet)

    [/td]


    [td]

    (DWG KC-3125)

    [/td]


    [td]

    Color 4

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    (DWG KC-3126)

    [/td]


    [td]

    Color 1

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    (DWG KC-3126)

    [/td]


    [td]

    Color 2

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    (DWG KC-3126)

    [/td]


    [td]

    Color 3

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    (DWG KC-3126)

    [/td]


    [td]

    Color 4

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]








    -------------------------------------------------------------
    I posted this on the MrExcel forum at the link below but never received any responses.
    https://www.mrexcel.com/forum/…ook-another-workbook.html


    Any help is greatly appreciated.

Participate now!

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