New "Run-time error '1004'" out of nowhere!

[Groves22]

I run a weekly report each Friday. I was on vacation last week. When I try to run the macro, I get the error. This has worked for years, but now crashed for some reason.
During my vacation, I was upgarded to MS Office 2007, could that be the issue?

    With PolicyD.Sheets(1)
        .Columns("D").Insert Shift:=xlToRight
        .Columns("D").NumberFormat = "General"
        .Range("D1") = "Prev Pol Number"
        .Columns("D").AutoFit
        .Activate
        [Red].Cells(2, 4).Resize(rCount - 1, 1).Formula = "OMIT FORMULA"[/Red]
        .Range(.Cells(2, 4), .Cells(rCount - 1, 4)).Value = .Range(.Cells(2, 4), .Cells(rCount - 1, 4)).Value
    End With

 

[PHV]

What is the value of rCount when the error raises ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Groves22]

What is the value of rCount when the error raises ?

42

 

[SkipVought]

Are you sure that it is the statement that you have emphasized in [red]red[/red]?

Could it be some other statement?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Groves22]

SkipVought

Are you sure that it is the statement that you have emphasized in red?

Could it be some other statement?

Yes. When the error comes up, I hit debug and that is the line it takes me to. I just don't understand why, all of a sudden, it quit working? I haven't changed a thing, except the MS Office upgrade (2003 to 2007).

 

[SkipVought]

What happens if you hit F8 at that point?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Groves22]

SkipVought

What happens if you hit F8 at that point?

The same error will reappear.

 

[SkipVought]

What happens if your COMMENT the .Activate statement to prevent the activation of the sheet???

    With PolicyD.Sheets(1)
        .Columns("D").Insert Shift:=xlToRight
        .Columns("D").NumberFormat = "General"
        .Range("D1") = "Prev Pol Number"
        .Columns("D").AutoFit[b]
 '       .Activate[/b]
        .Cells(2, 4).Resize(rCount - 1, 1).Formula = "OMIT FORMULA"
        .Range(.Cells(2, 4), .Cells(rCount - 1, 4)).Value = .Range(.Cells(2, 4), .Cells(rCount - 1, 4)).Value
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Groves22]

SkipVought

What happens if your COMMENT the .Activate statement to prevent the activation of the sheet???

That was something I added, when trying to see why this bombed. It made/makes no difference. It actually isn't in my code at the moment.

 

[SkipVought]

How are you determining that the value of rCount is 42, when your code is stopped in debug?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Groves22]

SkipVought
How are you determining that the value of rCount is 42, when your code is stopped in debug?

When I hover the mouse over rCount, 42 appears in the box.

However... I found the issue. It was a 2003 to 2007 upgrade issue. The query tool I use names the tab to the queries name. In 2003, it could be as long as it wanted. In 2007, it was cutting the name of the tab in half. I put in some code to rename the sheets to 'Sheet1' and everything appears to work now.

I just don't know why I was getting the 1004 error for that issue?

Thanks for looking into this for me, Skip and PHV!!

 

[SkipVought]

You mean that YOUR CODE names the query AND the sheet.

I would urge you to redesign your process to QueryTable.Refersh in one sheet and then APPEND the query resultset to the MASTER list that would contain ALL QUERY RESULTS. It would solve more than one issue or potential issue.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Groves22]

SkipVought
You mean that YOUR CODE names the query AND the sheet.

No. We use a tool called ShowCase query here. In the tool, you name the file it outputs to. ShowCase names the file and the tab name.

 

[SkipVought]

We use a tool called ShowCase query

YOUR code, where you actually coded it or not!!!

This behavior is not native behavior. Therefore, YOUR CODE (ie ShowCase) is causing this to happen.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Groves22]

YOUR code, where you actually coded it or not!!!

This behavior is not native behavior. Therefore, YOUR CODE (ie ShowCase) is causing this to happen.

Didn't mean to set you off, I'm sorry. When I hear "your code", I think of code I actually wrote.
ShowCase is a user-friendly tool that writes all the SQL as you select to fields/tables/data to pull from the system.
Thank for taking the time to view and help troubleshoot my problem, though! I am grateful for all the help I get on this site!