Copy Values Between 2 Worksheet With Offset

  • i am needing a way to place information in a template and copy this template for as many items as i have. As seen in the attached spreadsheet on sheet1 the template is 6 columns and 5 rows. The colors correspond to the information needed on sheet 2. this template includes data from row 3 on sheet 2. I need a macro that will copy this template and return the next row of data from sheet2. this has me stumped. Also the data changes on sheet, so it may have many rows or just a couple. Thanks for the help.

  • Re: Copy Data Loop


    i cant find one that applies to this situation. my problem is when i paste excel moves the cells down the number of rows i pasted. instead of a4 it becomes a9.

  • Re: Copy Data Loop


    zotee98 - Give this a shot and let me know how it goes.


    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]Use this revised code. It clears the original tables before making more.


  • Re: Copy Data Loop


    wow that worked great - thanks - this forum is sweet![hr]*[/hr] Auto Merged Post;[dl]*[/dl]


    why can i not do a -4 for "ws1.Range("D" & sht1Rows).Offset(-3, 0)" i am totally stump

  • Re: Copy Data Loop


    Quote from zotee98

    why can i not do a -4 for "ws1.Range("D" & sht1Rows).Offset(-3, 0)" i am totally stump


    I don't know, why can't you? Do you get an error? Does Offset(-4) take you off the page, i.e., if you are on row 3, Offset(-4) will take you to row 0, which doesn't exist.

  • Re: Copy Data Loop


    zotee98 - I take it your real tables are different from those in your example? The offset is based on column C, specifically I used the word BID to identify that last row of a table. With that, and since each table was 5 rows tall is the reason for my particular offsets. If you want to see what the address is of one of your offsets, right after the for statement use something like this. In this way, you can see the address of each offset and verify if that is where you want your value placed.


  • Re: Copy Data Loop


    Quote

    i cant find one that applies to this situation.

    That's because you used the Thread Title to state what you THINK your answer is and not what you are trying to do.
    [fa]*[/fa]

Participate now!

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