Ahhh! error message 1004

[Cath]

Hi all

Working on someone else's macros/ worksheets which is a real pain.

I just can't seem to get a macro to just copy a cell!! Seems stupid but it won't have it. I've tried on another made up sheet and it worked no problem. Can anyone think of something that is wrong with this sheet that is stopped it selecting a cell. I get runtime error 1004 activate method of range class failed every time I try to select range("e4").select. I've tried activating it, using activeworkbook.activesheet things that were suggested in other helps but to no avail

Here is the bit of code.

Dim labelnumber
Windows("basefilecolourPIP2.xls").Activate
Sheets("data").Select
Range("e3").Copy
Range("e4").select
Selection.PasteSpecial Paste:=xlValues
labelnumber = Range("E4").Value
MsgBox labelnumber
ActiveWindow.ActivateNext
Range("b6") = labelnumber
x = MsgBox("Is this the correct label number? " & labelnumber & Chr(13) & "Click OK to print or cancel to exit", vbOKCancel)
If x = vbOK Then
Selection.PrintOut
Else
Exit Sub
End If

Any help appreciated!

Cath ray:

 

[GlennUK]

Is E4 part of a merged cell range?

 

[Cath]

No, and it doesn't seem to matter which cell I select it won't work. I can do it manually but not with a macro

 

[GlennUK]

How are you starting your macro? Is it from a ControlToolbox button?

 

[sykes]

Cath

As it's the "Activate method" that the error message says is the failure, and the only line of your posted code which uses this command is:

Windows("basefilecolourPIP2.xls").Activate

Could it possibly be that this is the actual line at fault?
How do you know it's failing at the "Range("e4").select" line?

 

[Cath]

Its running from activate sheet in another file.

 

[Cath]

Hi

The range line highlights in yellow. I can get it work if I just use another sheet within the same file.

 

[sykes]

OK - It doesn't answer your question, but in order to solve the problem, how about changing the code slightly?
As the copy / paste lines only seem to make E4 equal E3, could you just alter the code thus:

Dim labelnumber
Windows("basefilecolourPIP2.xls").Activate
Sheets("data").Select

Range("e4").value=Range("e3").value

labelnumber = Range("E4").Value
MsgBox labelnumber
ActiveWindow.ActivateNext
Range("b6") = labelnumber
x = MsgBox("Is this the correct label number? " & labelnumber & Chr(13) & "Click OK to print or cancel to exit", vbOKCancel)
If x = vbOK Then
Selection.PrintOut
Else
Exit Sub
End If

 

[Leonard of Quirm]

Cath,
Where is the macro stored? Is it in a sheet object or within a module?

Ensure its the latter.

Matt

 

[Cath]

Hi all, I'm answering 2 questions here

I tried the suggestion but it doesn't work. The reason I used the copy and paste to start with is that I couldn't get the variable to pick up the value of the cell E3. Seeing as E3 is a formula I thought copying and pasting as a value would help

And the other question. Yes its in the sheet not a module. I have it set up as 'start on sheet activate' thing. Is this bad??

Cath