Hi,

I am getting this error:

Select Method of Range Class Failed



When I run my macro that copies a selected range from one worksheet to
another. The worksheet it is copying from has values that are linked to
another workbook. My code is as follows:

Range("C4:CR54").Select
Selection.ClearContents

Sheets("JAN06").Range("A4:AG60").Select
Sheets("Jan06").Range("A4:AG60").Copy
Sheets("3 Months").Range("A4:AG60").PasteSpecial
Paste:=xlPasteValues

Sheets("Feb06").Range("F4:AG60").Select
Sheets("Feb06").Range("F4:AG60").Copy
Sheets("3 Months").Range("AH4:BI60").PasteSpecial
Paste:=xlPasteValues

Sheets("Mar06").Range("F4:AJ60").Select
Sheets("Mar06").Range("F4:AJ60").Copy
Sheets("3 Months").Range("BJ4:CN60").PasteSpecial
Paste:=xlPasteValues


The ranges clearly exist and so do the workbooks yet it gives me that
error. Why is this?

Hi,

Is there a way to save a workbook so that when you open it again by double
clicking its ikon, you can have the same view settings (for example same
toolbox components displayed), even if sombody else used excell and changed
its settings.

In other words, I want to save the workbook so that it always opens with the
same view settings.

Thanks in advance
--
Ayse

I'm not an expert but try this:

Range("C4:CR54").Select
Selection.ClearContents

Sheets("JAN06").Activate
Range("A4:AG60").Select
Selection.Copy
Sheets("3 Months").Activate
Range("A4:AG60").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False


Sheets("Feb06").Activate
Range("F4:AG60").Select
Selction.Copy
Sheets("3 Months").Activate
Range("AH4:BI60").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False


Sheets("Mar06").Activate
Range("F4:AJ60").Select
Selection.Copy
Sheets("3 Months").Activate
Range("BJ4:CN60").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False


HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=498957

I'm no VBA expert but I believe that the code is blowing up because Sheet
Feb06 is not the active sheet. To cointinue using your code change it to:

Sheets("JAN06").Select
Range("C4:CR54").Select
Selection.ClearContents

Range("A4:AG60").Select
Selection.Copy
Sheets("3 Months").Range("A4:AG60").PasteSpecial
Paste:=xlPasteValues

Sheets("Feb06").Select
Range("F4:AG60").Select
Selection.Copy
Sheets("3 Months").Range("AH4:BI60").PasteSpecial
Paste:=xlPasteValues

Sheets("Mar06").Select
Selection.Copy
Sheets("3 Months").Range("BJ4:CN60").PasteSpecial
Paste:=xlPasteValues

However, you do not need to select anything to copy and paste it. Here is
our code re-written to avaid selecting:

Sub NewCode()
Application.ScreenUpdating = False

With Sheets("JAN06")
.Range("C4:CR54").ClearContents
.Range("A4:AG60").Copy
With Sheets("3 Months")
.Range("A4").PasteSpecial Paste:=xlPasteValues
End With
End With

With Sheets("Feb06")
.Range("F4:AG60").Copy
With Sheets("3 Months")
.Range("AH4").PasteSpecial Paste:=xlPasteValues
End With
End With

With Sheets("Mar06")
.Range("F4:AJ60").Copy
With Sheets("3 Months")
.Range("BJ4").PasteSpecial Paste:=xlPasteValues
End With
End With

Application.CutCopyMode = False
applciation.ScreenUpdating = True

End Sub



Sandy

with @tiscali.co.uk
"Naji" wrote in message
ups.com...
Hi,

I am getting this error:

Select Method of Range Class Failed



When I run my macro that copies a selected range from one worksheet to
another. The worksheet it is copying from has values that are linked to
another workbook. My code is as follows:

Range("C4:CR54").Select
Selection.ClearContents

Sheets("JAN06").Range("A4:AG60").Select
Sheets("Jan06").Range("A4:AG60").Copy
Sheets("3 Months").Range("A4:AG60").PasteSpecial
Paste:=xlPasteValues

Sheets("Feb06").Range("F4:AG60").Select
Sheets("Feb06").Range("F4:AG60").Copy
Sheets("3 Months").Range("AH4:BI60").PasteSpecial
Paste:=xlPasteValues

Sheets("Mar06").Range("F4:AJ60").Select
Sheets("Mar06").Range("F4:AJ60").Copy
Sheets("3 Months").Range("BJ4:CN60").PasteSpecial
Paste:=xlPasteValues


The ranges clearly exist and so do the workbooks yet it gives me that
error. Why is this?

Hi Ayse

You should start your own thread, that way people can focus on your
problem, by posting on somebody else's thread you might divert
attention from his or her problem and they might not get answered.

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=498957

You can only select a range on a worksheet that's selected.

So this will fail if Jan06 isn't the activesheet:

Sheets("JAN06").Range("A4:AG60").Select

So you could add:

Sheets("jan06").select
Sheets("JAN06").Range("A4:AG60").Select


But looking at your snippet of code, you could just drop the .selects and work
directly with the ranges:

Range("C4:CR54").ClearContents

Sheets("Jan06").Range("A4:AG60").Copy
Sheets("3 Months").Range("A4:AG60").PasteSpecial Paste:=xlPasteValues

Sheets("Feb06").Range("F4:AG60").Copy
Sheets("3 Months").Range("AH4:BI60").PasteSpecial Paste:=xlPasteValues

Sheets("Mar06").Range("F4:AJ60").Copy
Sheets("3 Months").Range("BJ4:CN60").PasteSpecial Paste:=xlPasteValues


===
And I'd use something like:

Sheets("Mar06").Range("F4:AJ60").Copy
Sheets("3 Months").Range("BJ4").PasteSpecial Paste:=xlPasteValues

If you only specify the top left corner of the range that's getting pasted, then
excel will match the dimension of the copied range. (It saves me from having to
count lots of columns!)



Naji wrote:

Hi,

I am getting this error:

Select Method of Range Class Failed

When I run my macro that copies a selected range from one worksheet to
another. The worksheet it is copying from has values that are linked to
another workbook. My code is as follows:

Range("C4:CR54").Select
Selection.ClearContents

Sheets("JAN06").Range("A4:AG60").Select
Sheets("Jan06").Range("A4:AG60").Copy
Sheets("3 Months").Range("A4:AG60").PasteSpecial
Paste:=xlPasteValues

Sheets("Feb06").Range("F4:AG60").Select
Sheets("Feb06").Range("F4:AG60").Copy
Sheets("3 Months").Range("AH4:BI60").PasteSpecial
Paste:=xlPasteValues

Sheets("Mar06").Range("F4:AJ60").Select
Sheets("Mar06").Range("F4:AJ60").Copy
Sheets("3 Months").Range("BJ4:CN60").PasteSpecial
Paste:=xlPasteValues

The ranges clearly exist and so do the workbooks yet it gives me that
error. Why is this?

--

Dave Peterson