Using a button to select cells on different worksheets

[Babymech]

I have some problems using a single button to copy cells from one sheet to
another - I get a runtime error 1004: "Select method of Range class failed".

The way I have it set up right now is that I have a button on sheet1 that
takes a String from sheet1 as input, copies Rows2:18 from sheet5, selects the
last row with content on sheet1, and pastes the copied material, then selects
Rows20:36 from sheet 5, selects the last row on sheet2, and pastes the new
copied cells. I've set it up as follows:

Sub Click(): Gets the String input from a cell on sheet1, and then calls
CopyToSheet1(Stringinput) and CopyToSheet2(Stringinput)

In CopyToSheet1(Stringinput) I have it do the following

Find the last row of sheet1:

Dim LastRow As Integer
LastRow = Worksheets("Sheet 1").Cells(ActiveSheet.Rows.Count,
"B").End(xlUp).Row + 2

Then I print the StringInput in a cell on sheet5, copy rows(2:18) from
sheet5, and finally paste my copied material:

Worksheets("Sheet 5").Rows("2:18").Copy
Worksheets("Sheet 1").Cells(LastRow, 1).Select
ActiveSheet.Paste

This works great. It manages to find the right cells on sheet5, and it
manages to find the last row on sheet1, and it pastes perfectly. I would like
to get rid of the extra step, and just have paste it directly rather than
selecting first and pasting to activesheet, but as far as I can tell it's not
possible to simply replace Copy with Paste ("object doesn't support this
property or method").

Anyway, that works fine. For copytosheet2, however, I get "Select method of
Range class failed" when it's trying to select the last row on sheet2. The
debug shows me that it's defined the lastrow properly (in the test case it's
defined as "29" on sheet2, whereas it's "23" on sheet1), but it simply can't
select it. Is this because the button is on sheet1 and the selection I want
is on sheet2? That doesn't seem to stop the .copy command which works fine;
can i get .paste to do this for me?

I tried to be as detailed as possible, and hopefully it was possible to
follow my description and you know how to help me. Thanks!

 

[Joel]

Your problem is you are mixing Rows with Cells. Excel allows you to copy
from a Row to a Row, a Column to a Column, or an Area to an Area. You can't
mix the 3. An area you can specify with either RANGE or CELLS. The area
also has to be the same size. I usually either specify the first cells of
the destination and let excel determine the number of rows and columns. Or I
specify the 1st Row or 1st Column. Excel won't let you copy a 3 x 4 area
into a 4 x 3 area. But excel will let you copy a 3 x 4 to a 9 x 4. Excel
will copy the same data 3 times.

so you can do a couple of things

1)
Worksheets("Sheet 5").Rows("2:18").Copy
Worksheets("Sheet 1").Rows(LastRow).Paste


2)

Worksheets("Sheet 5").Rows("2:18").Copy _
Destination:=Worksheets("Sheet 1").Rows(LastRow)

 

[Babymech]

Excellent, thanks. The second alternative solved it for me, but there are
still a few things that confuse me... first of all, I really don't get why
the function allowed me to paste into sheet1 - I was making the same mistake
there by mixing rows and cells. Secondly, I still can't get your first
alternative to work - though I've triple-checked that I'm putting everything
in there correctly, it still tells me that the object does not support this
property or method. It really works for you?

 

[Joel]

Worksheets("Sheet1").Rows(LastRow & ":" & 21).PasteSpecial

I didn't realize that paste also requires a destination. Instead
PasteSpecial will work. I don't use Paste that often because I usually use
the 2nd method.


You code worked because excel was smart enough that when you had the cell in
column A selected it knew what the row was suppose to be. Excel has error
checking to make sure the copy size mathes the destination size. Excel has
problem when you copy a row (in excel 2003) has 256 cells/columns and you try
to paste that into a row starting at column C two cells will extended past
the end of the worksheet. without the error checking excel will put column A
into column B, Column B into Column D and then won't know what to do with the
two extra cells.