Ahhh! error message 1004

Cath

Board Regular
Joined
Aug 10, 2005
Messages
156
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 :pray:
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Cath

Board Regular
Joined
Aug 10, 2005
Messages
156
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
How are you starting your macro? Is it from a ControlToolbox button?
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Aug 10, 2005
Messages
156

ADVERTISEMENT

Hi

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

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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

Board Regular
Joined
May 19, 2005
Messages
180
Cath,
Where is the macro stored? Is it in a sheet object or within a module?

Ensure its the latter.

Matt
 

Cath

Board Regular
Joined
Aug 10, 2005
Messages
156
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,387
Members
412,590
Latest member
Velly
Top