PDA

View Full Version : Method 'Select' of object '_Worksheet' failed



paulked
03-13-2009, 07:30 AM
I can only run any of the macro's below once. If I try to run a second time then Excel gives Run-time error '-2147417848(80010108)'


Sub Blem()
Selection.Copy
Sheets("Need").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
ActiveSheet.Paste
Sheets("Made").Select
Range("A" & BoatReturn.ListBox1.ListIndex + 2).Select
With ActiveCell
Cells(.Row, "B").Select
Application.CutCopyMode = False
Selection.Value = ("BLEM")
End With
'Call SortMade
'Call SortNeed
BoatReturn.Hide
End Sub

Sub Scrap()
Selection.Copy
Sheets("Need").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
ActiveSheet.Paste
Sheets("Scrap").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
ActiveSheet.Paste
Sheets("Made").Select
Range("A" & BoatReturn.ListBox1.ListIndex + 2).Select
With ActiveCell
Cells(.Row, "B").Select
Application.CutCopyMode = False
Selection.Value = ("SCRAP")
End With
'Call SortMade
'Call SortNeed
BoatReturn.Hide
End Sub

Sub UK1st()
Selection.Copy
Sheets("Need").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
ActiveSheet.Paste
Sheets("Made").Select
Range("A" & BoatReturn.ListBox1.ListIndex + 2).Select
With ActiveCell
Cells(.Row, "B").Select
Application.CutCopyMode = False
Selection.Value = ("UK1st")
End With
'Call SortMade
'Call SortNeed
BoatReturn.Hide
End Sub


Any help appreciated

Paul Ked

Bob Phillips
03-13-2009, 07:34 AM
Got a workbook that you can post?

paulked
03-13-2009, 07:49 AM
Hi and thanks for quick response.

Select the "Move boat back" button, select boat and then any of the buttons at the bottom of the form.

Many thanks

Paul Ked

mdmackillop
03-13-2009, 03:56 PM
Hi Paul,
I didn't get an error with your code.

If you avoid using Selection and work with the range directly, you can greatly simplify and speed up your code

Sub SortMade()
Sheets("Made").Columns("A:F").Sort Key1:=Sheets("Made").Range("D1"), Order1:=xlAscending, Key2:=Sheets("Made").Range("A1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1").Select

End Sub
Sub SortNeed()
Sheets("Need").Columns("A:F").Sort Key1:=Sheets("Need").Range("D1"), Order1:=xlAscending, Key2:=Sheets("Need").Range("A1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1").Select
End Sub

Sub Blem()
Selection.Copy Sheets("Need").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Cells(BoatReturn.ListBox1.ListIndex + 2, 2) = ("BLEM")
'Call SortMade
'Call SortNeed
BoatReturn.Hide

End Sub

Sub Scrap()
Dim Rng As Range
Set Rng = Selection
Rng.Copy Sheets("Need").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Rng.Copy Sheets("Scrap").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Cells(BoatReturn.ListBox1.ListIndex + 2, 2) = "SCRAP"

'Call SortMade
'Call SortNeed
BoatReturn.Hide

End Sub
Sub UK1st()
Selection.Copy Sheets("Need").Cells(Rows.Count, 1).End(xlUp).Offset(1)
Cells(BoatReturn.ListBox1.ListIndex + 2, 2).Value = ("UK1st")
BoatReturn.Hide
End Sub

paulked
03-23-2009, 07:17 AM
Many thanks for that, much neater!

Unfortunately, I am still having trouble. It seems that (using either method) I am running out of memory as Excel still crashes (I got a "Excel cannot complete this task with available resources. Choose less data or close other applications" error before the first crash)

Any ideas?

BTW, unlucky in the rugby last Sat :mkay