• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Insert rows pasting just values

C

CarolM

New Member
Is there a way to insert a row just pasting values? I have data from a drop down and want to copy and paste each time without copying the drop down along with it. The user selects the values and I have a macro that just copies it to a data tab to collect all information from each user. I just don't want to bring the drop down over..

Thank you you amazing excel people !!!
 
If you are putting the information at end of table, you would do something like
Code: 
Worksheets("Sheet1").Range("A2:D2").Copy
Worksheets("Sheet2").Cells(Rows.Count,"A").End(xlUp).Offset(1,0).PasteSpecial xlPasteValues

If you are inserting a row between other data, you need to insert the row first, and then copy and paste the information.
 
Thank you Luke. I am not very good with VB (but hoping to improve!). I am inserting and I did copy and paste values after insert but the drop down was still there.....
 
Hi Luke, I used your VB code and used it in an already created macro as I am still unsure of the syntax and it worked Perfectly! Thank you so much. Enjoy your day :)
 
Hi Carol,

Glad you were able to get it working. To explain the code...
First line is a defines a worksheet and it's range, and we Copy it. Pretty basic. :)

Second line is where we get a litte tricky. As you are inserting, not sure if this is what you used in end product, but I will explain. The line starts by looking in Sheet2, and we want a defined cell. the Cells object takes 2 arguments, row number and column number/letter. We'll start at bottom of worksheet. Since some sheets have 65536 rows, but XL 2007+ has more, we can't hardcode this, so we just tell XL to count number of rows in the sheet and use that. Then we use the End method. This is the same as when you use Ctrl+arrow key, to move to next cell with something in it. So, if we were at bottom of sheet and use End to go Up, we'll end up at last cell with data in in, for that column. But, this isn't where we want to paste our data. So, we use Offset. Offset also has 2 arguments, how many rows to offset, and how many columns to offset. We want to just move 1 row down.
Finally, we are ready to paste. We don't want to paste everything, so we use the PasteSpecial method, and tell it to paste only the values.
 
Thank you Luke. I have saved your explanation to use for future reference. I love this site and all the great information that it contains....thanks!
 
You must log in or register to reply here.
Back
Top