VBA Button Copy-Paste Non formatted cells, each paste goes onto next line in workbook

[dan91289]

Hi Excel Masters! - Attempting to write a VBA w/button that can be clicked that would permit roughly 18 specific cells to be copied and pasted into specific cells in specific columns. Each time the button is clicked, the data is copied and pasted in the line below the previous entry. Below is what I have written thus far, which copies the information and pastes it into the first line of where the data needs to be transferred to; however, I am unable to make it so each time the button is clicked, the data is entered in the line below the previous entry. Note: the location where the data is being pulled from is not going to change, only where the data is pasted will change. Here is what I have thus far:

Sub copy_a_range()

Range("C2").Copy
Range("B21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("C5").Copy
Range("C21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("D5").Copy
Range("F21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("D14").Copy
Range("G21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("D16").Copy
Range("I21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("F5").Copy
Range("L21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("F14").Copy
Range("M21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("F16").Copy
Range("O21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("H5").Copy
Range("R21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("H14").Copy
Range("S21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("H16").Copy
Range("U21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("H14").Copy
Range("S21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("J5").Copy
Range("X21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("J14").Copy
Range("Y21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("J16").Copy
Range("AA21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("L5").Copy
Range("AD21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("L14").Copy
Range("AE21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("L16").Copy
Range("AG21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("M5").Copy
Range("AI21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("N5").Copy
Range("Ak21").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False



End Sub

Thank you EXCEL Masters!

 

[Fluff]

Hi & welcome to MrExcel.
You can do that like

Sub copy_a_range()

Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Range("C2").Value

Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Range("C5").Value

Range("F" & Rows.Count).End(xlUp).Offset(1).Value = Range("D5").Value

End Sub

 

[dan91289]

Hi & welcome to MrExcel.
You can do that like

Sub copy_a_range()

Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Range("C2").Value

Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Range("C5").Value

Range("F" & Rows.Count).End(xlUp).Offset(1).Value = Range("D5").Value

End Sub

Hi and thank you for your response. I'm not following how this should be entered - Does it go before or after the code I've written? How might I integrate an Active XControl command button? I've included a picture a for visual purposes.

Ideally:
C2 populates in B21
C5 populates in C21
D5 - F21
D14 - G21
D16 - I21
...

The next time the button is clicked, those items will be pasted on line 22 in the respective columns.

Thanks!

 

[Fluff]

Does it go before or after the code I've written?

No, It replaces the code you posted, you just need to follow the same pattern for the rest of the cells.

 

[dan91289]

No, It replaces the code you posted, you just need to follow the same pattern for the rest of the cells.

Thank you - for learning purposes, what indicates that the first "paste" is to start on row 21? How do I integrate a button command?

 

[Fluff]

what indicates that the first "paste" is to start on row 21?

Nothing, because that is not what you asked for. You asked for

Each time the button is clicked, the data is copied and pasted in the line below the previous entry.

which is what the code will do.

How do I integrate a button command?

Use a shape, or Form Control button & then right click on it & assign Macro.

 

[dan91289]

Nothing, because that is not what you asked for. You asked for

which is what the code will do.


Use a shape, or Form Control button & then right click on it & assign Macro.

What would I need to add for it to commence on row 21? Thanks!

 

[Fluff]

Will all columns always end on the same row?

 

[dan91289]

Will all columns always end on the same row?

Yes - the VBA I shared initially outlines how I would like the items pasted. The next time pasted, rather than populate on line 21, it would be line 22.

 

[Fluff]

In that case try it like

Sub copy_a_range()
   Dim NxtRw As Long
   
   NxtRw = Range("B" & Rows.Count).End(xlUp).Row
   If NxtRw < 21 Then NxtRw = 21
   Range("B" & NxtRw).Value = Range("C2").Value
   Range("C" & NxtRw).Value = Range("C5").Value
   Range("F" & NxtRw).Value = Range("D5").Value
End Sub