PDA

View Full Version : Sub or Function not defined - switch off



Hoopsah
07-23-2008, 01:22 AM
Hi,

I am working on a spreadsheet, and I am trying to save a copy of the particular sheet using the macro:

Option Explicit
Sub SaveCopyOfSCWorksheet()
Dim FName
ActiveSheet.Unprotect
Sheets("Sewer Connection").Copy
Cells.Select
Range("A1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ChDir "I:\Customer Connections\Retail Connections Team\Retail Reporting - Business Improvement\Application Returns Folder\Sewer Connection" '<--Change to suit
FName = Application.GetSaveAsFilename(InitialFileName:="anynameyouwant")
If FName = False Then
ActiveWorkbook.Close False
Else
ActiveWorkbook.SaveAs FName & "xls"
ActiveWorkbook.Close False
End If
ActiveSheet.Protect
End Sub


It runs so far and then the error message Sub or Function not defined pops up. The problem seems to be that on the original sheet I have a cell that when clicked on gives a pop-up calendar, and when this macro runs - It copies the sheet and then can't find the calendar.

Is there anyway around this. Ideally just to switch off the calendar and save the value of the cell instead.

Cheers in advance for any pointers - Happy to post the workbook if it helps,

Cheers Guys

Hoopsah

RichardSchollar
07-23-2008, 05:00 AM
Hi

Rather than copy the sheet itself, why don't you just copy the contents of the sheet to a fresh workbook? That way you'll avoid copying any code in the worksheet's code module.

Richard

Hoopsah
07-23-2008, 05:29 AM
Hi Richard,

the main reason was because I incorporated this save procedure before in another program and it worked fine.

I reckon it is something to do with the pop-up calendar in this one, so I figured if I could disable that within the save procedure then everything would be ok.

I'll keep trying....................

Bob Phillips
07-23-2008, 06:34 AM
Try this



With ActiveWorkbook.VBProject.VBComponents("Sheet2").CodeModule
.DeleteLines 1, .CountOfLines
End With

Hoopsah
07-23-2008, 06:46 AM
Sorry Bob,

where would I put that bit of coding?

The code I supplied above is more or less what I have tried to use. Do I insert your code into it somewhere or replace it with your code.

Sorry for being dumb!

Gerry

Hoopsah
07-25-2008, 04:49 AM
Sorry!!

Just trying to bump this question

Cheers

Hoopsah

Bob Phillips
07-25-2008, 04:54 AM
I think you would pput it just before the activeworkbook.saveas.

Hoopsah
07-25-2008, 05:51 AM
Hi Bob,

I have attached a copy of the sheet that is giving me the problems.

I think it has something to do with the code for the page where I call upon a calendar, that when I begin running the save macro it cannot find the module I am quoting.

Hope it makes sense,

Cheers Bob

Gerry

Hoopsah
07-29-2008, 02:03 AM
Now posted on Office experts forum.

http://www.theofficeexperts.com/forum/showthread.php?p=34186#post34186

Bob Phillips
07-29-2008, 02:31 AM
Sorry Gerry, I forgot this one.

Just opened the work
book and immediately got an error as the selection event references a procedure OpenCalendar, which it can't find.

What is the underlying issue?

Hoopsah
07-29-2008, 02:44 AM
Hi Bob,

I inserted the code to call up the calendar within the spreadsheet 'View Code' option.

It should and does call up a pop up calendar form, however, when I try to save it using the code above it copies the data onto a new seperate worksheet but then cannot find the calender form.

I was hoping there was a way I could disable this in the save macro, or should I put the piece of coding elsewhere.

If it makes it easier I can post the entire workbook.

Cheers Bob

Bob Phillips
07-29-2008, 03:12 AM
The entire workbook would be better, that routine is misisng as is the calendar control.

Hoopsah
07-29-2008, 03:40 AM
Hi Bob,

please find the workbook attached,

cheers

Gerry

Bob Phillips
07-29-2008, 04:00 AM
I think is what you want Gerry.



Sub SaveCopyOfSCWorksheet()
Dim FName
ActiveSheet.Unprotect
Sheets("Sewer Connection").Copy
Cells.Value = Cells.Value
With ActiveWorkbook.VBProject.VBComponents("Sheet6").CodeModule
.DeleteLines 1, .CountOfLines
End With
ChDir "I:\Customer Connections\Retail Connections Team\Retail Reporting - Business Improvement\Application Returns Folder\Sewer Connection" '<--Change to suit
FName = Application.GetSaveAsFilename(InitialFileName:="anynameyouwant")
If FName = False Then
ActiveWorkbook.Close False
Else
ActiveWorkbook.SaveAs FName & "xls"
ActiveWorkbook.Close False
End If
ActiveSheet.Protect
End Sub

Hoopsah
07-29-2008, 06:06 AM
Hi Bob,

sorry about this, I feel as though this is turning into a saga now.

I tried the new code but I get an error message:

Run time error '7'
Out of memory

when I click debug it is highlighting the line : Cells.Value = Cells.Value

Bob Phillips
07-29-2008, 06:45 AM
That often happens with programming, some problems are not immediately resolved.

How does this fare



Sub SaveCopyOfSCWorksheet()
Dim FName
ActiveSheet.Unprotect
Sheets("Sewer Connection").Copy
Cells.Select
Range("A1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With ActiveWorkbook.VBProject.VBComponents("Sheet6").CodeModule
.DeleteLines 1, .CountOfLines
End With
ChDir "I:\Customer Connections\Retail Connections Team\Retail Reporting - Business Improvement\Application Returns Folder\Sewer Connection" '<--Change to suit
FName = Application.GetSaveAsFilename(InitialFileName:="anynameyouwant")
If FName = False Then
ActiveWorkbook.Close False
Else
ActiveWorkbook.SaveAs FName & "xls"
ActiveWorkbook.Close False
End If
ActiveSheet.Protect
End Sub

Hoopsah
07-29-2008, 07:09 AM
Hi Bob,

would you believe that takes me back to the original error message - function not defined!!!

Bob Phillips
07-29-2008, 08:21 AM
Which function is it saying not defined?

Hoopsah
07-30-2008, 03:54 AM
Hi Bob,

it is highlighting this area again.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$D$11:$E$11"
Call OpenCalendar
End Select
End Sub

Hoopsah
07-31-2008, 01:21 AM
:doh:

Hi Bob,

I have reverted back to my original code and taken out the function to call the calendar. (I can get round this by putting in =TODAY() and copying and pasting values)

However, with my original code, If I run the macro it will save a copy of the sheet called anynameyouwant, but if I then run it again, and try to save as the same name my excel crashes.

Is there a way around this??

Ideally, I would actually like the save name to be whatever is in cell D9 as this will be more of a unique reference. Although, it may be possible that it too will be a repeated reference.

the code I am now using is:

Option Explicit
Sub SaveCopyOfWorksheet()
Dim FName
ActiveSheet.Unprotect
Sheets("Sewer Connection").Copy
Cells.Select
Range("B10").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ChDir "I:\Customer Connections\Retail Connections Team\Retail Reporting - Business Improvement\Application Returns Folder\Sewer Connection" '<--Change to suit
FName = Application.GetSaveAsFilename(InitialFileName:="anynameyouwant")
If FName = False Then
ActiveWorkbook.Close False
Else
ActiveWorkbook.SaveAs FName & "xls"
ActiveWorkbook.Close False
End If
ActiveSheet.Protect
End Sub

Hoopsah
07-31-2008, 01:22 AM
Hi Bob,

I have reverted back to my original code and taken out the function to call the calendar. (I can get round this by putting in =TODAY() and copying and pasting values)

However, with my original code, If I run the macro it will save a copy of the sheet called anynameyouwant, but if I then run it again, and try to save as the same name my excel crashes.

Is there a way around this??

Ideally, I would actually like the save name to be whatever is in cell D9 as this will be more of a unique reference. Although, it may be possible that it too will be a repeated reference.

the code I am now using is:

Option Explicit
Sub SaveCopyOfWorksheet()
Dim FName
ActiveSheet.Unprotect
Sheets("Sewer Connection").Copy
Cells.Select
Range("B10").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ChDir "I:\Customer Connections\Retail Connections Team\Retail Reporting - Business Improvement\Application Returns Folder\Sewer Connection" '<--Change to suit
FName = Application.GetSaveAsFilename(InitialFileName:="anynameyouwant")
If FName = False Then
ActiveWorkbook.Close False
Else
ActiveWorkbook.SaveAs FName & "xls"
ActiveWorkbook.Close False
End If
ActiveSheet.Protect
End Sub

Any pointers?

Cheers Bob

Gerry