VBA CODE TO TRANSFER DATA

  • Hi all,


    I am back again on this topic with more information and hopefully you can help.


    When saving document 'New Quote Form' i want to run a macro that will copy data from sheet 'DataFeed' into sheet 'QuoteLog' of document 'QuoteLog'. All documents will be open when the macro is run.


    The data i need transferred is all from New quote form into Quote log.

    A2 to I2 , A3 to I3 , and then A4 to I4 into the next 3 empty rows in the quote log document, but i need it to skip out the d column, so no data is copy and pasted from the D column. The reason being is there is a formula in this box on the quote log document.


    When the macro is run a second time i need the same data copied again, but then pasted into the next 3 empty rows.


    So first save copies data into rows 2 3 and 4, next save it copies the same cells into the rows 5 6 and 7 etc.


    I appreciate any help given.

  • Place this macro in the code module for ThisWorkbook in the "New Quote Form" workbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. When you save the workbook, the macro to copy/paste the data will run automatically.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps,


    This works for the first time it is saved, but then when the document is saved a second time it doesn't paste the right data. What i have is a sheet that pulls data from various places on a different sheet to bring it into the same row. So in my data feed sheet A2 A3 and A4 all are cell r4 from sheet 1. When the macro is run a second time it copies across the data from r7 into the cells a5-7 instead of just using the data from my data feed sheet.

  • I'm not sure I follow. The ranges that are copied are hard coded so they shouldn't change. Can you attach copies of both files (de-sensitized if necessary) and explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • So my datafeed sheet pulls data from sheet 1.


    So a1 on datafeed is equal to r4 on sheet 1

    then b2 on datafeed is equal to az 30 on sheet 1

    this continues with varying cells


    when the macro is run a second time even ( i used the same data to show an example) it pulls all the cells 3 rows down instead. so its essentially saying a1 on datafeed is equal to r7 on sheet 1 when the cell is equal to r4 still it hasnt changed

  • I have given two docs for your review, i need the data pasted from data feed sheet (Quote from workbook) onto the quotelog1 when the other workbook is saved. when it is then saved again at a later time i need it to paste underneath the data already in the quotelog1


    Thanks

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi,


    Based on Mumps ' proposal ... a couple of tiny adjustments should do the job ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks Carim,


    Now my last question (i hope) is there a macro that can be created to copy data from active workbook (range is a2:i999) that will then paste the data into another open workbook (title Quote Book) into the next set of empty rows. Once pasted it clears the range a2:i999 from the active workbook. I will assign this macro to a button in the active workbook


    thanks again

  • Now my last question (i hope) is there a macro that can be created to copy data from active workbook (range is a2:i999) that will then paste the data into another open workbook (title Quote Book) into the next set of empty rows. Once pasted it clears the range a2:i999 from the active workbook. I will assign this macro to a button in the active workbook


    The code i need to create for the above will be stored in QuoteLog


    The code i have which mumps helped me with is stored elsewhere