Method 'Range' of object'_Global' failed

[lirab]

HI
I have a module which I am cloning to move data to another worksheet depending on the original cell value.

I have run the macro 4 times changing the cell refernece in the if statement and the range of data to be copied.

THe fifth time it runs I get the Methid range error when it tries to paste the data. I do not understand why. Can you help?

Here is the code. It stops at the Range("AA").select location:
Public Sub bowler5()
'bowler5
Sheets("bowlers").Select
If Range("G6").Value = 1 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("B3").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 2 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("G6").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 3 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("L3").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 4 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("Q3").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 5 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("V3").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 6 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("AA").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 7 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("AF").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 8 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("AK").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 9 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("AP").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 107 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("AU").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 11 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("AZ").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 12 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("BE").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 13 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("BJ").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 14 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("BO").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 15 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("BT").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 16 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("BY").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 17 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("CD").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 18 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("CI").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 19 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("CN").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 20 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("CS").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 21 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("CX").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 22 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("DC").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 23 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("DH").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 24 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("DL").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 25 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("DR").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 26 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("DW").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 27 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("EB").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 28 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("EG").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 29 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("EL").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 30 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("EQ").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 31 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("EV").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 32 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("FA").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 33 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("FF").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 34 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("FK").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 35 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("FP").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 36 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("FU").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 37 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("FZ").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 38 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("GE").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 39 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("GJ").Select
ActiveSheet.Paste
ElseIf Range("G6").Value = 40 Then
Sheets("weekly_input").Select
Range("D15:G15").Select
Selection.Copy
Sheets("score_history").Select
Range("GO").Select
ActiveSheet.Paste
End If
End Sub

Thanks
Lirab

 

[asking]

Hi lirab,

Possible reasons ~

Range("AA") Is not a normal valid range unless it is a name that has been defined in the name box for each sheet ~ and refers to a range

If you wish to select all of Column AA it would be ~ Range("AA:AA")
If you wish to paste data to Column AA starting at the first cell ~ Range("AA1") would suffice.

I noticed that you had more ranges ie. Range("AF").Select
You would also have to change all of them as well.

 

[repairman615]

Hello,

Possibly "A:A" instead of AA.

I am not real sure as I cannot test for you result.

 

[repairman615]

Here might be a structure to consider.
This should be better as it eliminates 'select' and copies and pastes without the clipboard.




<font face=Courier New><SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Range("G6").Value<br>    <SPAN style="color:#00007F">Case</SPAN> 1<br>        Sheets("weekly_input").Range("D15:G15").Copy Destination:=Sheets("score_history").Range("B3")<br>    <SPAN style="color:#00007F">Case</SPAN> 2<br>        Sheets("weekly_input").Range("D15:G15").Copy Destination:=Sheets("score_history").Range("G6")<br>    <SPAN style="color:#00007F">Case</SPAN> 3<br>    <br>    <SPAN style="color:#00007F">Case</SPAN> 4<br>    <br>    <SPAN style="color:#00007F">Case</SPAN> 5<br>    <br>    <SPAN style="color:#00007F">Case</SPAN> 6<br>    <br>    <SPAN style="color:#00007F">Case</SPAN> 7<br>    <br>    <SPAN style="color:#00007F">Case</SPAN> 8<br>    <br>    <SPAN style="color:#00007F">Case</SPAN> 9<br>    <br>    <SPAN style="color:#00007F">Case</SPAN> 107<br>    <SPAN style="color:#007F00">'''and continue</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN></FONT>




Follow the first two examples in the select case.

 

[lirab]

Thank you
That made perfect sense and did work.
Lirab

Hi lirab,

Possible reasons ~

Range("AA") Is not a normal valid range unless it is a name that has been defined in the name box for each sheet ~ and refers to a range

If you wish to select all of Column AA it would be ~ Range("AA:AA")
If you wish to paste data to Column AA starting at the first cell ~ Range("AA1") would suffice.

I noticed that you had more ranges ie. Range("AF").Select
You would also have to change all of them as well.