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:
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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,722
How are you starting your macro? Is it from a ControlToolbox button?
 

sykes

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

Forum statistics

Threads
1,171,565
Messages
5,876,216
Members
433,185
Latest member
ptr009

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top