1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Paste as values and move to next empty cell

Discussion in 'VBA Macros' started by fareedexcel, Jul 9, 2018.

  1. fareedexcel

    fareedexcel Member

    Messages:
    78
    Dear Experts,

    I have a table where by entering a number, data will auto populate.

    Now the requirement is by clicking a button, those data to be pasted as values in another cell.

    Then when entering the next number and clicking the button, the new data to be pasted in the next empty cells. Please help.

    This is the code I used to paste the data from the auto populated cell,

    Code (vb):
    Sub Pastecellvalues()
    'Set the cells' values equal to another to paste values

        'Set a cell's value equal to another cell's value
    Sheets("Data Capture").Range("B1").Copy
    Sheets("Data Capture").Range("G4").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues
    Sheets("Data Capture").Range("B2").Copy
    Sheets("Data Capture").Range("H4").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues
    Sheets("Data Capture").Range("B7").Copy
    Sheets("Data Capture").Range("I4").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues
    Sheets("Data Capture").Range("B8").Copy
    Sheets("Data Capture").Range("J4").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues
    Sheets("Data Capture").Range("B9").Copy
    Sheets("Data Capture").Range("K4").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
           
    End Sub
     

    Attached Files:

  2. Ashhu

    Ashhu Active Member

    Messages:
    214
    Hi Check attached sheet. click copy button.

    Attached Files:

    Thomas Kuriakose and fareedexcel like this.
  3. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    Hi !
    Sure ? As your attachment result is not pasted by values ‼

    If data are really to be pasted as values,
    that needs only a couple of codelines … (Edit : just with one codeline !)
  4. fareedexcel

    fareedexcel Member

    Messages:
    78
    In my original sheet. My data will populate with a vlookup formula (vlookuping the ERP No)
  5. fareedexcel

    fareedexcel Member

    Messages:
    78
    Thanks Ashwin. The code is working as per my requirements. Is there any way to avoid duplication. In case if I press the button twice it is copying twice.
  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    No matter as your attachment not shows data pasted by values ‼
    If really only values are required it needs only a single codeline !
    fareedexcel likes this.
  7. fareedexcel

    fareedexcel Member

    Messages:
    78
    Could you please mention the codeline and to avoid duplication of entries
    Last edited by a moderator: Jul 10, 2018
  8. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    According to initial explanation & attachment :​
    Code (vb):
    Cells(Rows.Count, 7).End(xlUp)(2).Resize(, 5).Value = Application.Index([B1:B9], [{1,2,7,8,9}])
  9. Ashhu

    Ashhu Active Member

    Messages:
    214
    Incredible!!
    [​IMG]
    Marc L likes this.
  10. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    LoL ‼ Thanks Ashhu !

    But nothing incredible, just respecting TEBV rule via
    mixing Excel basics (Index formula & array) and VBA …
    fareedexcel and Ashhu like this.
  11. fareedexcel

    fareedexcel Member

    Messages:
    78
    Thanks Marc and Ashwin. How duplicates can be avoided?
  12. fareedexcel

    fareedexcel Member

    Messages:
    78
    Hi Ashwin,

    In the VBA code you have mentioned with the Offset rule, what if I need to paste a particular value to a particular cell instead of moving to the next blank cell.

    Code (vb):
    Sub copypasvalue()


    ActiveSheet.Range("D8").Copy
    ActiveSheet.Cells(ActiveSheet.Rows.Count, "D").End(xlUp).Offset(1).PasteSpecial xlPasteValues

    Application.CutCopyMode = False


    End Sub
    I need to paste the D8 value in D11 for example not in the next blank row.
  13. Marc L

    Marc L Excel Ninja

    Messages:
    4,258


    [D11].Value = [D8].Value
  14. Ashhu

    Ashhu Active Member

    Messages:
    214
    Follow Marc L response.

    [D11].Value = [D8].Value

Share This Page