Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Copy paste special into last row of another worksheet [Solved/Closed]

Report
-
Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
 -
Hello,

Thank you for taking your time to read my questions.

My question is the following any form of help will be greatly appreciated.

I am trying to use check boxes in order to copy and paste values over to another worksheet automatically.

This means multiple items need to append onto the other worksheet leaving a row between eachother.

Is there something i can do to make this work?

this is the code i am currently using, but instead of having a specified area of selection when copying too, i would like it to pick up the row below the last used row

Private Sub CheckBox1_Click()
Worksheets("WebADI").Range("C19:Q272").value = Worksheets("Allocation").Range("K9:Y262").value
End Sub
Private Sub CheckBox2_Click()
Worksheets("WebADI").Range("C274:Q536").value = Worksheets("Allocation").Range("K273:Y526").value
End Sub

Thank you for any responses recieved.

See more
Related:

2 replies

Answer 1 / 2
Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
 454
Hi Roy,

Try this:
Worksheets("Allocation").Range("K9:Y262").copy Worksheets("WebADI").Range("C"&rows.count).end(xlup).offset(1,0)

When you want an empty row between pasted data change the offset to (2,0).

Best regards,
Trowa
2
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report

Hi Trowa,

thank you very much for your response its made a huge dent in what i am trying to do. It worked in regards to pasting the information over, however i will need to paste as values in order to stop all the formulas ref'ing out, can you please help me further in pasting the same code as values.

Thank you Roy
Answer 2 / 2
Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
 454
Hi Roy,

To paste as values use this: 
Worksheets("Allocation").Range("K9:Y262").copy 
Worksheets("WebADI").Range("C"&rows.count).end(xlup).offset(1,0).pastespecial Paste:=xlPasteValues


This will keep copied information in Excels memory. To clear memory use this after all pasting actions in the code:
application.cutcopymode = false

Using the macro recorder is a very handy tool to figure out how to do certain things in vba. So if for example you want to know how to paste format only, record macro, do the actions, stop recording and check the code.

This is just a general tip, so don't be afraid to ask here.

Best regards,
Trowa
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!