Macro error 1004

[toolmantwo]

I had a spreadsheet with several Macros that did work fine, it was built about 1999. Now when I open in Excel version 2003 SP3 I get this error (Run time Error '1004' Application Defined or object defined error).

I also tried building an new spreadsheet with a new macro in this Excel version 2003. I used record and the first two worked ok, then the third macro also failed with this same error. I do not know VBA at all. I have only used record. It also seems to be random as sometimes the Macro works OK.

Any suggestions?

 

[SkipVought]

Hit the Debug Button and tell us on what statement it is.

Please post your code.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[toolmantwo]

Sub Picture75Leaves_Click()
'
' Picture75Leaves_Click Macro
' Macro recorded 10/16/2007 by jfw2
'

'
Sheets("Calendar").Select
ActiveCell.Offset(-18, -3).Range("A1").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

This is the code from the one macro that is giving me the problem.

 

[SkipVought]

You never said WHICH STATEMENT is erroring?????

    Sheets("Calendar").Select
    ActiveCell.Offset(-18, -3).Range("A1").Select
    Selection.Copy[b]
    ActiveSheet.Paste[/b]
    Application.CutCopyMode = False

What is this macro attempting to do? It is horrible!

So you click on this PIC and it takes you to Sheet Calendar.

Then, based on the ActiveCell (which could be ANYTHING, maybe even an unexpected cell), goes UP 18 rows (what if the activecell were in row 17 or less???) and 3 cells to the left (what if the activecell were in columns A or B???) Range("A1") is meaningless.

Then you COPY that cell and paste it right where it is?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

What Skip was asking for is, what line of code is highlighted after you get the error.

But looking at it, I'd assume that it is this one:

ActiveCell.Offset(-18, -3).Range("A1").Select

What that line says is as follows:
- From the activecell,
[tab]- Move up 18 rows
[tab]- Move left 3 columns
[tab][tab]-Select that[/b] cell.

If you have cell A1 selected - or any cell less than 18 rows from the top or less than 3 columns from the left - then the code will fail.

Rather than depend on having the proper cell selected, it is better to just tell excel which cell to copy.

If you want to copy cell A1, then you would change this:

    Sheets("Calendar").Select
    ActiveCell.Offset(-18, -3).Range("A1").Select
    Selection.Copy

to this

    Sheets("Calendar").Range("A1").Copy

The next two lines of code are fine as you have them.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Heh. I didn't notice the selects. This macro doesn't do anything, does it?

Perhaps it would be better to post the code that existed before that just started throwing an error, rather than the code you recorded just recently.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[toolmantwo]

ActiveCell.Offset(-18, -3).Range("A1").Select

Is the one highlited in Yellow, which I assume is the error.

I was not realy done with the macro. I want to stop at that sheet then wait until I select one cell which will have a date. I want to select a week day date based on when I think the task can be completed. Then I want copy to paste this same date in a the prior selected cell for the related task.

I just now noticed I have the ActiveSheet.paste, which I did not want at this time.

 

[SkipVought]

Check out the InputBox type for a cell reference (8).

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[toolmantwo]

ok I found one file that has not been altered. It runs just part way and I get the error (Ambiguous name detectedicture2_Click).

It is not a large file, can I send the file to you?

I can give you my email address.

 

[SkipVought]

Use the Watch Window to determine what NAME the message is refering to.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]