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

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

Report
-
Posts
2673
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 26, 2020
 -
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
2673
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 26, 2020
 446
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
Thank you
2
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
2673
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 26, 2020
 446
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