Error when running 2003 Macro in 2010. HELP PLEASE :-)

[Mister H]

Hi All:</SPAN>

Our Office was recently upgraded from Excel 2003 to Excel 2010 and I am having an issue with a macro that when run stops Excel from working. When I click the Refresh button it starts running but then I get the following error:</SPAN>

Microsoft Excel has stopped working (my only option is "Close the Program")</SPAN>

If I go into VBA and step through the code it works for me but not when I use the button. I have tons of other codes in the spreadsheet but I have ripped them all out and still get the error. I think the error is when it tries to delete the sheet titles Journal. If I remove the line of code that deletes that sheet and rename the copied sheet to Journal NEW instead of Journal the code works. ?????????????????????</SPAN>

Here are my references in VBA:</SPAN>


References - VBAProject</SPAN>

Visual Basic For Applications</SPAN>
Microsoft Excel 14.0 Object Library</SPAN>
Microsoft Forms 2.0 Object Library</SPAN>
Microsoft HTML Object Library</SPAN>
Microsoft Internet Controls</SPAN>
Microsoft Visual Basics for Applications Extensibility 5.3</SPAN>
OLE Automation</SPAN>

Here is the code that craps out when run using a button:</SPAN>
​

Sub REFRESH_Journal()
    Sheets("Journal").Select
    Application.DisplayAlerts = False
    [COLOR=#ff0000]ActiveWindow.SelectedSheets.Delete
[/COLOR]    Sheets("Journal (BackUp)").Visible = True
    Sheets("Journal (BackUp)").Copy Before:=Sheets(9)
    Sheets("Journal (BackUp)").Select
    Sheets("Journal (BackUp)").Visible = xlVeryHidden
  
    Sheets("Journal (BackUp) (2)").Select
  [COLOR=#ff0000]  Sheets("Journal (BackUp) (2)").name = "Journal"
[/COLOR]    
'Opportunity to SAVE a copy to your HardDrive
Answer = MsgBox("Do you need to UNHIDE the Hidden Rows on the worksheet?" & vbNewLine & vbNewLine & _
                   "If you do then click YES if not then click No.", vbYesNo, "Reveal Hidden ROws???")
If Answer = vbYes Then
'Remove Filter (Shows all rows again)
    Sheets("Deposit Worksheet").Select
    ActiveSheet.Unprotect "invoice"
    Selection.AutoFilter
    Range("C4:D4").Select
   
    ActiveSheet.Protect "invoice"
    
    Sheets("Journal").Select
    Range("M2").Select
If Answer = vbNo Then
End If
End If
Application.DisplayAlerts = True
End Sub

Any suggestions?

THANKS,
Mark

 

[Mister H]

Darn I was HOPING there was a nice strightforward solution to my issue. ANYONE??? ANything I can try?

THANKS Agaun,
Mark

 

[RoryA]

I can't see anything obviously wrong there but you could try this:

Sub REFRESH_Journal()    Application.DisplayAlerts = False
    Sheets("Journal").Delete
    Application.DisplayAlerts = True


    With Sheets("Journal (BackUp)")
        .Visible = xlSheetVisible
        .Copy Before:=Sheets(9)
        .Visible = xlSheetVeryHidden
    End With
    DoEvents
    Sheets("Journal (BackUp) (2)").Name = "Journal"

 

[Mister H]

Hi Rory:

THANKS for the code but unfortunately I get the same error. I THINK I have found away around it. Who know if it will continue to work. So far I have not found any joy in my Office 2010 Experience. The code that i posted works on my computer that was upgraded manually but not on others that were upgraded via remote (Radia Push?). ANyway here is what I have found that works (at least for today)

I have split the code into 2. First I am renaming the sheet I want to delete and then deleting it at the end of my code.

Makes no sense to me but seems to work:

Sub REFRESH_Journal()</SPAN>

[B]Sheets("Journal").Select[/B]</SPAN>[B]
Sheets("Journal").name = "DELETE"
[/B]</SPAN>
Sheets("Journal (BackUp)").Visible = True</SPAN>
Sheets("Journal (BackUp)").Copy Before:=Sheets(9)</SPAN>
Sheets("Journal (BackUp)").Select</SPAN>
Sheets("Journal (BackUp)").Visible = xlVeryHidden
</SPAN>
Sheets("Journal (BackUp) (2)").Select</SPAN>
Sheets("Journal (BackUp) (2)").name = "Journal"</SPAN>

Application.ScreenUpdating = True</SPAN>
Application.GoTo Reference:="R2C10"</SPAN>
Application.GoTo Reference:="R2C19"</SPAN>
Application.DisplayAlerts = True</SPAN>

'Opportunity to SAVE a copy to your HardDrive</SPAN>
Answer = MsgBox("Do you need to UNHIDE the Hidden Rows on the worksheet?" & vbNewLine & vbNewLine & _</SPAN>
"If you do then click YES if not then click No.", vbYesNo, "Reveal Hidden ROws???")</SPAN>

If Answer = vbYes Then</SPAN>

'Remove Filter (Shows all rows again)</SPAN>
Sheets("Deposit Worksheet").Select</SPAN>
ActiveSheet.Unprotect "invoice"</SPAN>
Selection.AutoFilter</SPAN>
Range("C4:D4").Select</SPAN>

'Hide CCPay Section as MAG does not use it</SPAN>
Range("HideCCPay").EntireRow.Hidden = True</SPAN>

ActiveSheet.Protect "invoice"</SPAN>

Sheets("Journal").Select</SPAN>
Range("M2").Select</SPAN>


If Answer = vbNo Then</SPAN>

End If</SPAN>
End If</SPAN>

[B]Call DeleteOldJournal[/B]</SPAN>End Sub

</SPAN>
Sub DeleteOldJournal()</SPAN>

Application.DisplayAlerts = False</SPAN>
Sheets("DELETE").Delete</SPAN>
Application.DisplayAlerts = True</SPAN>

End Sub

</SPAN>I can't wait to see whats next in my 2010 adventure

Have a GREAT day ALL