Paste as values and move to next empty cell

[fareedexcel]

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,

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

 

[Ashhu]

Hi Check attached sheet. click copy button.

 

[Marc L]

Hi !

those data to be pasted as values

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 !)
​

 

[fareedexcel]

In my original sheet. My data will populate with a vlookup formula (vlookuping the ERP No)

 

[fareedexcel]

Hi Check attached sheet. click copy button.

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.

 

[Marc L]

In my original sheet. My data will populate with a vlookup formula (vlookuping the ERP No)

No matter as your attachment not shows data pasted by values ‼
If really only values are required it needs only a single codeline !
​

 

[fareedexcel]

Could you please mention the codeline and to avoid duplication of entries

 

[Marc L]

According to initial explanation & attachment :​

Cells(Rows.Count, 7).End(xlUp)(2).Resize(, 5).Value = Application.Index([B1:B9], [{1,2,7,8,9}])

 

[Ashhu]

According to initial explanation & attachment :​

Cells(Rows.Count, 7).End(xlUp)(2).Resize(, 5).Value = Application.Index([B1:B9], [{1,2,7,8,9}])

Incredible!!

 

[Marc L]

LoL ‼ Thanks Ashhu !

But nothing incredible, just respecting TEBV rule via
mixing Excel basics (Index formula & array) and VBA …
​

 

[fareedexcel]

Thanks Marc and Ashwin. How duplicates can be avoided?

 

[fareedexcel]

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.

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.

 

[Marc L]

[D11].Value = [D8].Value​

 

[Ashhu]

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.

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.

Follow Marc L response.

[D11].Value = [D8].Value