Erase Code

I have a spreadsheet that runs code when it opens to populate the data. I
need it to automatically erase the code once complete so that when I save the
workbook with a new name; the code does not rerun when management opens the
workbook to view the data.

See www.cpearson.com/excel/vbe.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Bill" <Bill@discussions.microsoft.com> wrote in message
news:6108BB86-E61E-4CA7-94F3-31E1AF046074@microsoft.com...
>I have a spreadsheet that runs code when it opens to populate
>the data. I
> need it to automatically erase the code once complete so that
> when I save the
> workbook with a new name; the code does not rerun when
> management opens the
> workbook to view the data.

Add the routine at the end of your code to

SELECT ALL
COPY
PASTE SPECIAL, VALUES only.

I do not remember the exact coding sequence for above, but if you record a
macro and then do the above steps, Excel will show you what to do.
Sir Metro

I am getting a object not define error. Can you help?

"Chip Pearson" wrote:

> See www.cpearson.com/excel/vbe.htm
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Bill" <Bill@discussions.microsoft.com> wrote in message
> news:6108BB86-E61E-4CA7-94F3-31E1AF046074@microsoft.com...
> >I have a spreadsheet that runs code when it opens to populate
> >the data. I
> > need it to automatically erase the code once complete so that
> > when I save the
> > workbook with a new name; the code does not rerun when
> > management opens the
> > workbook to view the data.
>
>
>

You can write an if statement at the beginning of your
code to say something like:

If ActiveWorkbook.Name <> "YourWorkbookName.xls" Then
'Code Here
Else
'Other condition code here
End If

You can use = or <>.

This way when you change the name of the file, the code
will not run the next time because the workbook name has
changed. Im not sure if leaving this code in the workbook
is exactly what you want, but the reults will be to where
the code will not run.


Todd


>-----Original Message-----
>I have a spreadsheet that runs code when it opens to
populate the data. I
>need it to automatically erase the code once complete so
that when I save the
>workbook with a new name; the code does not rerun when
management opens the
>workbook to view the data.
>.
>

You need a reference to the extensibility library. In VBA, go to
the Tools menu, choose References, and scroll down until you find
"Microsoft Visual Basic For Applications Extensibility Object
Library". Put a check next to this item.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Bill" <Bill@discussions.microsoft.com> wrote in message
news:4EF7A306-7580-4184-B7C7-64BC9F534E74@microsoft.com...
>I am getting a object not define error. Can you help?
>
> "Chip Pearson" wrote:
>
>> See www.cpearson.com/excel/vbe.htm
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "Bill" <Bill@discussions.microsoft.com> wrote in message
>> news:6108BB86-E61E-4CA7-94F3-31E1AF046074@microsoft.com...
>> >I have a spreadsheet that runs code when it opens to populate
>> >the data. I
>> > need it to automatically erase the code once complete so
>> > that
>> > when I save the
>> > workbook with a new name; the code does not rerun when
>> > management opens the
>> > workbook to view the data.
>>
>>
>>

Would it be an option to have the code in another file and just manually
start running your program on the main file? or keep the code in the file,
but copy the resultant data over to a new file after your data processing
program completes?

"Todd Huttenstine" wrote:

> You can write an if statement at the beginning of your
> code to say something like:
>
> If ActiveWorkbook.Name <> "YourWorkbookName.xls" Then
> 'Code Here
> Else
> 'Other condition code here
> End If
>
> You can use = or <>.
>
> This way when you change the name of the file, the code
> will not run the next time because the workbook name has
> changed. Im not sure if leaving this code in the workbook
> is exactly what you want, but the reults will be to where
> the code will not run.
>
>
> Todd
>
>
> >-----Original Message-----
> >I have a spreadsheet that runs code when it opens to
> populate the data. I
> >need it to automatically erase the code once complete so
> that when I save the
> >workbook with a new name; the code does not rerun when
> management opens the
> >workbook to view the data.
> >.
> >
>