Macro to copy row from one sheet... Paste in first blank row on another

rocksoliddigital

New Member
Joined
Mar 29, 2013
Messages
2
Hey guys! Maybe you can assist me with a macro...
When a button is pushed, I am trying to copy an entire row from one sheet and paste it into another sheet's first blank row. I would like this to happen whenever the button is pressed.
I found this, but I haven't had any luck incorporating it in my worksheets. When I try to adjust it to fit my sheets it doesn't work. I'm kind of a macro newbie.

Sub CpyAct()

ActiveCell.EntireRow.Copy Destination:=Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Date
End Sub


A bit more info on what I'm trying to accomplish. I have a quote generating form. Those inputs all go to a single row. I want to be able to copy that row's information to a separate data list. So you enter one quote, copy the info over, enter another quote, copy the info over to the next row, and so on. So the end of the day it could have a dozen separate quotes all accounted for.

The copy from row is sheet is "Quote_Data" Row 1. Can be whole row but A-AP is where the info is.
Paste to row is sheet "Quote_List" and can start at A1 and continue down.

Thanks for your help!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,436
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try this:
Code:
Sub AddQuote()
Dim vA As Variant, nR As Long
vA = Sheets("Quote_Data").Range("A1:AP1").Value
If Not IsEmpty(Sheets("Quote_List").Range("A1")) Then
    nR = Sheets("Quote_List").Range("A" & Rows.Count).End(xlUp).Row + 1
    Sheets("Quote_List").Range("A" & nR).Resize(1, UBound(vA, 2)).Value = vA
Else
    Sheets("Quote_List").Range("A1").Resize(1, UBound(vA, 2)).Value = vA
End If
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,606
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Would this one-liner suffice?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> MoveQuote()<br>  Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 42).Value = Sheets("Quote_Data").Range("A1:AP1").Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

rocksoliddigital

New Member
Joined
Mar 29, 2013
Messages
2
Beautiful! It does exactly what I needed.
Thanks so much guys! I was having the worst time trying to get it working.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top