Microsoft Excel 2007 application defined or object-defined error 1

[makeitwork09]

I am using Microsoft 2007 in Compatibility Mode

Sub HandlePreviousRecords()
   Dim answer As String


   answer = MsgBox("Running this will delete the prior records. Do you
want to continue?", Buttons:=vbYesNo + vbQuestion)
   If answer = vbYes Then

       Workbooks("compass_test.xls").Worksheets("COMPASS").Range("A1",
ActiveCell.SpecialCells(xlLastCell)).Clear

       'delete the old records
        Call CompassQT
   End If

End Sub

The above code was working fine until I added and executed the code below

Sub saveCompassFeed()
'
' saveCompassFeed Macro
' This macro copies the contents of the COMPASS tab to the designated
folder on the network drive
'
' Keyboard Shortcut: Ctrl+s
'
   Dim FD As String 'From  date
   FD = Format(Range("accounting_date").Value, "mm_dd_yyyy") & " DATA" &
Format(Now(), "mm dd yyyy hh mm AMPM")


   'Copy  worksheet
   Application.ScreenUpdating = False
   Dim wb As Workbook
   Dim NewShtName As String
   NewShtName = "COMPASS " & FD

   Workbooks("compass_test.xls").Worksheets("COMPASS").Copy
   Set wb = ActiveWorkbook
   wb.Worksheets("COMPASS").Name = NewShtName

'    from [URL unfurl="true"]http://www.ozgrid.com/forum/showthread.php?t=65552[/URL]
   ActiveWorkbook.SaveAs Filename:="\\Real-Estate\Reports\" & _
   "COMPASS " & FD, FileFormat:=xlNormal, Password:="", _
   WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

   MsgBox "A file was created and saved on S:STAFF - vol_1\INVEST\Reports
as " & NewShtName, , "File Successfully Saved"

   Application.ScreenUpdating = True

End Sub

Now that the above code has been executed, while testing the code, the
first subroutine returns the following error

Application defined or object-defined error


I tried setting

Workbooks("compass_test.xls").Worksheets("COMPASS")

to ws as
Worksheet variable and breaking out

Workbooks("compass_test.xls").Worksheets("COMPASS").Range("A1",
ActiveCell.SpecialCells(xlLastCell)).Clear

to

set wsInitial = Workbooks("compass_test.xls").Worksheets("COMPASS")
wsInitial..Range("A1", ActiveCell.SpecialCells(xlLastCell)).Clear

but got the following error when I did that

Method range of object_worksheet


Any assitance is much appreciated

 

[SkipVought]

Hi,

This statement will not work...

   wb.Worksheets("COMPASS").Name = NewShtName

You change a workbook name by SaveAs method.

Please tell me what you're attempting to do with the .Clear method. What area are you clearing? What area do you NOT want to clear?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Hi Skip

First, unrelated to this post, after you helped me with another post I went out and bought the Excel 2007 VBA Reference book by Wrox. I'm still getting my feet wet, but I wanted to let you thank you for your help and give the updat about the book.

Now, back to the post.

The spreadsheet is the result set of a query from stored sprocedure. Each month when the first macro is run (there's a macro in between the two above CompassQT) I'm clearing out the contents from the previous month and then running the query using the call to CompassQT. I did this in case the result set is fewer rows than the previous month.

 

[SkipVought]

You told me WHY you are doing this.

That was not my question. There were 2 specific questions.

clearing out the contents from the previous month

Does that mean that absolutely nothing is left on the sheet or not?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Yes. It clears the result set, that is all the records on the work sheet, queried the previous month. I want to clear everything on the page. ADO is used to connect to SQL Server to execute a stored procedure.

 

[SkipVought]

I want to clear everything on the page.

Workbooks("compass_test.xls").Worksheets("COMPASS").clear

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Thanks Skip

That doesn't work. Clear does not seem to be a method that belongs to the worksheet object.

 

[SkipVought]

sorry

Workbooks("compass_test.xls").Worksheets("COMPASS").cells.clear

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

It's ok. This only seems to be a problem because of the SaveAs method. Temporarily I've removed it, closed out of Excel (seems liked the SaveAs code was trapping something), then reopened the spreadsheet and reran it with no issues.

What I want to do is save the spreadsheet without using the Filedialog and with a specified worksheet, not the whole workbook.

 

[SkipVought]

What I want to do is save the spreadsheet without using the Filedialog and with a specified worksheet, not the whole workbook.

wb.Worksheets("COMPASS").Copy
with ActiveWorkbook 
   .SaveAs '...............
   .close
end with
'now what do you do with workbook wb?????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Hi Skip - that worked. Thanks.

To answer your question the saveas portion is in my original post. I save the specified worksheet to a network drive.

Thanks for you help.