Copy cells into continuous rows after running Macro

[Ejimenez]

I currently have a macro that copy the information from one sheet to another and places them in row 2, being that row 1 has headers which is Great. However, how to I format the code to paste the information on the next row that follows on sheet2 after the macro is ran twice. For example, Macro ran once, paste to row 2. Macro is ran a second time, paste that information to row 3. Macro is ran a third time, information is paste to row 4. Right now it only overwrites the information on sheet2. My code is below.

Sub testmacro()
Application.CutCopyMode = 0

' TestMacro Macro


Range("Q4").Select
Selection.Copy
Sheets("Summary").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Partner Score Form").Select
Range("F2:I2").Select
Selection.Copy
Sheets("Summary").Select
Range("B2:E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Partner Score Form").Select
Range("D4:E4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("F2:G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Partner Score Form").Select
Range("Q2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Partner Score Form").Select
Range("I4:N4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("I2:N2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Partner Score Form").Select
Application.CutCopyMode = False
End Sub


YOU WOULD BE A LIFE SAVER!!!

​

 

[Michael M]

Try this UNTESTED

Sub testmacro()
Dim lr As Long
lr = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("Q4").Copy
Sheets("Summary").Range("A" & lr).PasteSpecial Paste:=xlPasteValues
Sheets("Partner Score Form").Range("F2:I2").Copy
Sheets("Summary").Range("B" & lr).PasteSpecial Paste:=xlPasteValues
Sheets("Partner Score Form").Range("D4:E4").Copy
Sheets("Summary").Range("F" & lr).PasteSpecial Paste:=xlPasteValues
Sheets("Partner Score Form").Range("Q2").Copy
Sheets("Summary").Range("H" & lr).PasteSpecial Paste:=xlPasteValues
Sheets("Partner Score Form").Range("I4:N4").Copy
Sheets("Summary").Range("I" & lr).PasteSpecial Paste:=xlPasteValues
End Sub

 

[Michael M]

A bit cleaner

Sub testmacro()
Dim lr As Long
lr = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("Q4").Copy
Sheets("Summary").Range("A" & lr).PasteSpecial Paste:=xlPasteValues
Range("F2:I2").Copy
Sheets("Summary").Range("B" & lr).PasteSpecial Paste:=xlPasteValues
Range("D4:E4").Copy
Sheets("Summary").Range("F" & lr).PasteSpecial Paste:=xlPasteValues
Range("Q2").Copy
Sheets("Summary").Range("H" & lr).PasteSpecial Paste:=xlPasteValues
Range("I4:N4").Copy
Sheets("Summary").Range("I" & lr).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Ejimenez]

Thank you for the response. I tried the to write a loop code and it worked but its pasting the value in sheet1.. how do I paste the value to column C but in Sheet2. The code is below:

Submacro1()

Range("B3").select
Selection.copy
Dim Currentrow as integer
currentrow = 1
Do While (True)
Dim CellLine = "C" & currentrow

If (Len(Range(CellLine).Text) = 0) then
Range (CellLine).Select
ActiveSheet.paste
Exit Do
End if
currentRow = CurrentRow +1
Loop
End Sub

Try this UNTESTED

Sub testmacro()
Dim lr As Long
lr = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("Q4").Copy
Sheets("Summary").Range("A" & lr).PasteSpecial Paste:=xlPasteValues
Sheets("Partner Score Form").Range("F2:I2").Copy
Sheets("Summary").Range("B" & lr).PasteSpecial Paste:=xlPasteValues
Sheets("Partner Score Form").Range("D4:E4").Copy
Sheets("Summary").Range("F" & lr).PasteSpecial Paste:=xlPasteValues
Sheets("Partner Score Form").Range("Q2").Copy
Sheets("Summary").Range("H" & lr).PasteSpecial Paste:=xlPasteValues
Sheets("Partner Score Form").Range("I4:N4").Copy
Sheets("Summary").Range("I" & lr).PasteSpecial Paste:=xlPasteValues
End Sub

 

[Michael M]

Is this related to the current question ??
If not, let's solve the problems one at a time !!