VB to Post to New Line per Button Click

Question

I recorded a macro, linked to a button, on say sheet1. The object of what I need done:

When the button is pressed, certain cells are selected and copied to a "summary" page on another sheet.

Sheet1 has a drop down that shows certain information. So after data is selected from the drop down, the user will push the button and post that data to the summary sheet.

The macro works fine (please note I am a VBA noob), but I need assistance in adding functionality that after every button press, it copies the data on the next line - in other words, if data is in row 1 already, it must place the data in row 2, and so on.

The VB code I have is as follows:

Sub Test()
'
' Test Macro
'

'
Range("C32:N32").Select
Selection.Copy
Sheets("Summary").Select
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks 
_
    :=False, Transpose:=False
Sheets("Comm Payable").Select
Range("C3:D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks 
 _
    :=False, Transpose:=False
Sheets("Comm Payable").Select
Range("N1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks 
_
    :=False, Transpose:=False
Range("B4").Select
Sheets("Comm Payable").Select
Range("O1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("O1").Select
 End Sub

Please could someone assist with the addition described above?

Much appreciated!

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It is highly recommended to first apply this technique to all recorded code before continuing in changing the code. — Pᴇʜ, Apr 30 '19 at 11:15

SJR · Accepted Answer · 2019-04-30T11:59:50.830

It's imperative you read the link posted by PEH as you can considerably shorten and speed up your code. I think this does what you want.

Sub Test()

Dim r As Long

r = WorksheetFunction.Max(Sheets("Summary").Range("D" & Rows.Count).End(xlUp).Row + 1, 3)

Sheets("Comm Payable").Range("C32:N32").Copy
Sheets("Summary").Range("D" & r).PasteSpecial Paste:=xlPasteValues

Sheets("Comm Payable").Range("C3:D3").Copy
Sheets("Summary").Range("B" & r).PasteSpecial Paste:=xlPasteValues

Sheets("Comm Payable").Range("N1").Copy
Sheets("Summary").Range("C" & r).PasteSpecial Paste:=xlPasteValues

Sheets("Comm Payable").Range("O1").ClearContents

End Sub

As an aside, transferring values directly is more efficient than copying and pasting and here is an example of that.

With Sheets("Comm Payable").Range("C32:N32")
    Sheets("Summary").Range("D" & r).Resize(.Rows.Count, .Columns.Count).Value = Value
End With

Thank you! The code works perfectly. I will read through link PEH shared. Thank you both! — Gray Meiring, Apr 30 '19 at 11:31

VB to Post to New Line per Button Click

1 Answers1