Copy and pasting ranges

R

RFrechette

I am new to programming in Excel, so forgive me if there is an obvious answer
to this question.

I have created a button that will copy 2 ranges from 2 different
spreadsheets and paste them onto a 3rd spreadsheet. The button is located on
the 3rd spreadsheet. The code copies and pastes the 1st range, then moves
the curser to a new line, then copies and pastes the 2nd range.

The code works fine for the 1st range and repositioning the curser, but when
it gets to the 2nd range, it give me an error.
"Run-time error ‘1004’:
The information cannot be pasted because the Copy area and the paste area
are not the same size and shape."

Then it brings me to the ActiveSheet.Paste line in the 2nd Paste code. (See
full code below.)

Worksheets("Danka").Range("DANKA").Copy
→→ ActiveSheet.Paste

If I take out the code for the 2nd range the code works fine with no errors
and the curser is left on a new line.

Here's the code I'm using:

Private Sub Ranges_Click()

Worksheets("Charrette").Range("CHARRETTE").Copy
ActiveSheet.Paste

Application.SendKeys ("{ESCAPE}")
Application.SendKeys ("{DOWN}")
Application.SendKeys ("{END}")
Application.SendKeys ("{DOWN}")
Application.SendKeys ("{DOWN}")

Worksheets("Danka").Range("DANKA").Copy
ActiveSheet.Paste

End Sub

Can someone please help me?

Thank you,

Rachel
 
S

Sandy

Not Sure what you are trying to do with the send key methods, but try
this code out in a copy of you workbook and see if it does what you
want it to do. This will find the last row in Column A in your
activesheet then move down 1 row and paste your first range, then move
down one more and paste the second range.

Private Sub Ranges_Click()
Dim aSheet As Worksheet
Set aSheet = ActiveSheet
aSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
Worksheets("Charrette").Range("CHARRETTE").Copy
aSheet.Paste

aSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
Worksheets("Danka").Range("DANKA").Copy
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub


hope this is what you were looking for
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top