Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a button to select cells on different worksheets
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a button to select cells on different worksheets
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" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a button to select cells on different worksheets
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" wrote: 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" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a button to select cells on different worksheets
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. "Babymech" wrote: 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" wrote: 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I wan to select a button | Excel Discussion (Misc queries) | |||
autofill button is not appearing when I select cells in Excel 2007 | Excel Worksheet Functions | |||
Creating/Naming New Worksheets Based on Select Cells in Master Worksheet | Excel Worksheet Functions | |||
Excel 2003 is missing toolbar button select visible cells | Excel Worksheet Functions | |||
select specific cells and consolidate same over many worksheets | New Users to Excel |