adding OnSaveAs to existing VBA script

I have the following script with replaces all formulas with values.  Now 
I need for the script to execute when the user selects the 'Save As' 
file function.  Please advise.

Sub FormulasToValues()
'
' Replace all formulas with values
'

WCount = Worksheets.Count
For i = 1 To WCount
   If Worksheets(WCount - i + 1).Visible Then
     Worksheets(WCount - i + 1).Select
     RCount = ActiveCell.SpecialCells(xlLastCell).Row
     CCount = ActiveCell.SpecialCells(xlLastCell).Column
     For j = 1 To RCount
       For k = 1 To CCount
         Worksheets(WCount - i + 1).Cells(j, k) = Worksheets(WCount - i 
+ 1).Cells(j, k).Value
       Next k
     Next j
   End If
Next i

End Sub


thanx in advance,
~julz

0
jhbiz (27)
5/20/2004 3:16:38 PM
excel 39879 articles. 2 followers. Follow

1 Replies
182 Views

Similar Articles

[PageSpeed] 19

Hi
have a look at the workbook_beforesave event. See:
http://www.cpearson.com/excel/events.htm

--
Regards
Frank Kabel
Frankfurt, Germany


Julz wrote:
> I have the following script with replaces all formulas with values.
> Now I need for the script to execute when the user selects the 'Save
> As' file function.  Please advise.
>
> Sub FormulasToValues()
> '
> ' Replace all formulas with values
> '
>
> WCount = Worksheets.Count
> For i = 1 To WCount
>    If Worksheets(WCount - i + 1).Visible Then
>      Worksheets(WCount - i + 1).Select
>      RCount = ActiveCell.SpecialCells(xlLastCell).Row
>      CCount = ActiveCell.SpecialCells(xlLastCell).Column
>      For j = 1 To RCount
>        For k = 1 To CCount
>          Worksheets(WCount - i + 1).Cells(j, k) = Worksheets(WCount -
> i + 1).Cells(j, k).Value
>        Next k
>      Next j
>    End If
> Next i
>
> End Sub
>
>
> thanx in advance,
> ~julz

0
frank.kabel (11126)
5/20/2004 3:29:40 PM
Reply:

Similar Artilces:

Send All emails in Outbox and Quit using VBA
Hi, I have MS Access creating emails objects in Outlook using a macro. I want to send them all and close outlook after. 1-Open Outlook 2-Send all emails in the outbox folder 3-Close Outlook How can I achieve it using VBA? Using command buttons does not seem to work since my Outlook is in French, I'm using Outlook 2003 SP3. Thank You Stephane You're not trying to use command button captions, are you? Use the IDs instead, with the FindControl method. They're language-neutral. The ID for Send All should be 5577. -- Sue Mosher, Outlook MVP Author o...

adding VBA
Hi all-- I am REALLY new at access and found a great VBA code for cascading combo boxes. The only problem is I do not know where / how to install this code. Any suggestions would be VERY greatly appreciated. Thank you in advance Lawrence Dodge Perhaps a sample would be useful. On my website is a small sample database called: CascadingComboBoxes.mdb (http://www.rogersaccesslibrary.com/forum/topic389.html), which illustrates several variations. At the very least, it will show you where to put the code. Look in the Event properties of the combo boxes. -- --Roger Ca...

Hide built-in name when alternate name exists
This option appears in Style pane Options dialog box. Could you please give me an example on how it works? -- geotso ---- ---- --- -- -- - - Please, remove hyphens to contact me ---- ---- --- -- -- - - You can assign an alias (or "alternate name") to a style. Open the Modify Style dialog, and at the end of the style's name type a comma followed by the desired alias. You might want to do this to make the built-in style names describe what they're used for in this particular template or document. For example, you could change...

Document Connection crashes when adding a sharepoint site #2
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Trying to use Document Connection to connect to a sharepoint site. I select Add a Location -> Connect to Sharepoint Site. I get the menu that says "To access a file on a SharePoint site, enter the location of the SharePoint site." I enter the site URL: <https://sharepoint.uky.edu/UKIT/SAPSupport/default.aspx> and my userid/password and click Connect. It goes Ka-Boom and I get an error panel. I get the same result when I enter the site URL without the final filename: <https://sharepoint.uky...

Drawing in VBA
I have “inherited” a number of legacy worksheets that use, I believe, the pre Excel 97 via VBA drawing object model to plot complex diagrams on a number of sheets. These work perfectly in all versions of Excel up and to Excel 2003, but now give problems when running under Excel 2007 (although the VBA compiles OK) . In Excel 2007 the positioning and rotation of the arcs (and to some extent other drawing items). are totally different in Xl 2007 A simply example of the code I have is show below: Sub DrawArc() With ActiveSheet.Arcs.Add(10, 10, 200, 200) With...

Accessing named ranges in Excel from VBA
Can anyone tell me if and how I can access a named range on a workshee in a VBA procedure? The value of the cell is read from a record with vlookup, I need to b able to access it in a procedure in VBA as input in a new record. Thankx, Bas -- Bas ----------------------------------------------------------------------- Basz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1234 View this thread: http://www.excelforum.com/showthread.php?threadid=39458 Worksheets("Sheet1").Range("myName") You can get the Value property if it is a single cell, o...

DoneEx XCell Compiler
Can anyone comment on the VBA protection of DoneEx XCell Compiler? I asked DoneEx and they say they just hide the VBA code from view; they don't compile it to binary. Any thoughts on how much risk there is that someone could hack the protection and view my code? http://doneex.com/index.php?option=com_content&task=section&id=10&Itemid=43 Thanks, Greg If you are thinking of going in that direction I'd recommend Orlando Magalh�es Filho's XL to Exe http://orlando.mvps.org/#XLtoExe Regards, Peter T "Greg Lovern" <gregl@gregl.net&g...

top N values per group using VBA
Hello, I am trying to follow an example for finding top N values using VBA given at this link: http://support.microsoft.com/kb/210039/en-us#appliesto I've had success with this in the Northwind example database (with my own tables or with Northwind tables) ... But in my own databases (with my own tables) I get "Compile error: User'defined type not defined" with ", db As Database" highlighted in the code when I compile in the module and "Compile error.in query expression '(((Orders.OrderDate)>=NthInGroup([Customers].[CustomerID],5)))'."...

adding a semicolon
I have a column of over 500 9 digit numbers. What I need is to add a semicolon at the end of each individual number in its's cell - these number references will be eventually copied into another application to retrieve their associated data. I have tried to record a macro in order to do this but it only replicates the same numbers (with the semi colon) each time it is run. Is there a way of of doing this ? ActiveCell.FormulaR1C1 = "'385743341;" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "'538712414;" Active...

Adding back a server
I mistakingly removed a server from the First Administrative Group/Servers folder in the system manager. Does anyone know how to add it back? "Bellerophon" <anonymous@discussions.microsoft.com> wrote: >I mistakingly removed a server from the First >Administrative Group/Servers folder in the system >manager. Does anyone know how to add it back? reinstall the server. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm ...

Adding LLC to another set of words
I was wondering if there was a way to take multiple words, and add an LLC to the end of each word quickly. I have tried to use the add in paste special but it didn't work. Is there another way. Thanks How many words, Phil? Find and Replace won't work? ************ Anne Troy www.OfficeArticles.com "Phil Tukey" <phil@washfruit.com> wrote in message news:Oa6NXzNqFHA.2364@tk2msftngp13.phx.gbl... >I was wondering if there was a way to take multiple words, and add an LLC >to the end of each word quickly. I have tried to use the add in paste >special but it ...

Adding one column based on results from another column
I have a sheet that looks similar to this... Name1 60 Name2 0.25 Name3 5 Name4 0.15 I want to search on Name# and add all the numbers in the next colmn - does anyone know how... You might want to learn the subtotal feature: http://www.officearticles.com/excel/getting_subtotals_in_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "msbutton27" <msbutton27@discussions.microsoft.com> wrote in message news:8D15E0F3-AF3C-41C5-AFCE-ECCB43F85612@microsoft.com... >I have a sheet that looks similar to this... > > Name1 60 > Name2...

Exchange scripting question
I created some scripting to process all the incoming emails on an inbox. It works fine. Since all the processings happen in the Folder_OnMessageCreated, the question is what really happens if one event is still processing the last arrived email(s), there are new emails coming. Will this fire a new event? How does Exchange scripting agent work on this? Since I'm calling a DLL to grab all emails in the inbox, do some DB updates and then move these emails to a different folder, I certainly do not want to have multiple events running at the same time on the same emails. Thanks....

VBA code question?
Hi everyone, I have this simple 3 subs vba code. It runs 8 times and each solution has 24 values that is put in one column of 24 raws. So, the 1st solution found gets written at N2 to N25....and so on. The problem I have is this: The 1st solution that gets written from N2-N25, later it gets removed; just the 1st solution! What could be causing this in the code? Thanks in advance. Option Explicit Option Base 1 Sub Pareto() Dim cell As Range, ModelCounter As Integer, JobNr As Integer Dim ret As Integer Dim ARngSolution As Range Application.ScreenUpdating = False ModelCou...

VBA Question
Hi, I have a form with a combobox control that list some sizes. They are: 12 18 20 22 I have another text box for keying a custom size, such as 12'2". What I would like to do is have the combobox default to the next size (in this case 18). Here's the code I have used so far (it does a little more than what I am asking for but to give an idea): Dim MCUST As String MCUST = Me.custwidth If Str(Me.widthck) <> Me.custwidth Or Len(Me.custwidth) <> Len(Me.widthck) Then Me.widthck.Value = Int(Val(MCUST)) + 1 If InStr(Int(Me.widthck) / 2, ".&...

Adding same info
I have two worksheets that have the same info on one as the other such as names. To cut my work load in half I would like to know how to insert the name in one form and it automactly insert the name in the other worksheet. How do I do this? Thank Kenny Dee Hold down SHIFT-key and select both sheets ..........then enter the name in your selected cell and it will go in that cell on both sheets........... Vaya con Dios, Chuck, CABGx3 "Kenny Dee" <kennydee@sbcglobal.net> wrote in message news:#Ym$27IPFHA.3788@tk2msftngp13.phx.gbl... > I have two worksheets that have t...

Outlook 2003
Hi Using OUTLOOK 2003. Replying to a message automatically adds the email address to my contacts folder. I cannot locate the setting to turn this off. Any ideas? TIA KIWI There is no such setting. Outlook does not and cannot do this. Perhaps you are confusing the autocompletion cache with your Contacts Folder? -- Russ Valentine [MVP-Outlook] "KIWI" <john_hastie@nospanhotmail.com> wrote in message news:%23FPhMUjjFHA.2484@TK2MSFTNGP15.phx.gbl... > Hi > > Using OUTLOOK 2003. > > Replying to a message automatically adds the email address to my contacts &...

Outlook adding Reviewing Toolbar to Excel files
2003 on XP Everytime I receive an Excel file via Outlook, when I open the workbook the Reviewing toolbar appears unbidden. How can I stop this very annoying behaviour? I have asked in the Excel newsgroup with only a macro as a solution, not the best. There must be a setting in Outlook that can be turned off. Hope this helps DeanH Outlook has nothing to do with how other applications are opening the attachments that you have received. This remains an Excel issue and not an Outlook issue. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http:...

Changing existing data in database to title case
I have a nonprofit database that I am trying to change cities names to title case instead of all caps. I was sent an empty database that has a an update query that I can run to convert the case in tblCities. I tried to save this database to my hard drive and go through the backdoor privileges to Import the query into bearbase. I have no idea how to do this. The query doesn't even show up. Any help would be appreciated. On Sat, 6 Feb 2010 16:50:01 -0800, Jack <Jack@discussions.microsoft.com> wrote: >I have a nonprofit database that I am trying to change cities na...

merging ranges in VBA
i am running this code, sub page_setup Set SourceWB = Workbooks("book1.xls") With SourceWB.Sheets("sheet1") ' alot of other coding, but this seems to be the problem area .Range("K2:L2").Merge .Range("K3:L3").Merge .Range("K4:L4").Merge .Range("K5:L5").Merge .Range("K6:L6").Merge .Range("K7:L7").Merge end with end sub ________________________________ sub page_info Set SourceWB = Workbooks("book1.xls") With SourceWB.Sheets("sheet1") .Range("K7").Value = FormatPercent(.Range(&q...

Adding Fields in DM Fails (again)
Hi I know there are a number of threads on this, but my problem seems slightly different: I have tried to add a new schema field to the Product object in DM. It is the first time I have added a new field to this entity, although I have added new fields to others. I get an error that the addition has failed and tpo refer to the event log. There are two entries: dmLog: sp_repladdcolumn failed. - Failed to add column 'CFStest' to table 'ProductBase'. and dmLog: Failed to add new String attribute (CFStest) to Product entity. I then found that I could not add a new field t...

A few VBA questions
Hi, I'm currently trying to teach myself VBA. I may have set myself a rather ambitious problem, and I'm wondering if anyone can help. There are a few queries. All the queries center around the same report that is generated by another program - the locations of headers can change column each time the report is generated, and the number of rows change each time. A simplistic view of the report is as follows: A B C 1.Origin Destination %Margin 2. ABC DEF 5 3. GHI JKL 10 4...

To Generate 500 almost identical VBA Lines?
Hi there I have to create 500 lines of almost identical VBA code. Code is Range("y10").GoalSeek Goal:=ActiveSheet.Range("z10").Value, ChangingCell:=Sheets("FTSE").Range("x10") Range("y11").GoalSeek Goal:=ActiveSheet.Range("z11").Value, ChangingCell:=Sheets("FTSE").Range("x11") Range("y12").GoalSeek Goal:=ActiveSheet.Range("z12").Value, ChangingCell:=Sheets("FTSE").Range("x12") and so on untill Range("y500").GoalSeek Goal:=ActiveSheet.Range("z500").Valu...

VBA Customizations
Is this the correct newsgroup to post Great Plains Report Writer VBA Customizations questions? Don't see any other GP newsgroups. yes - go ahead and post HS "Chris Patten" <ChrisPatten@discussions.microsoft.com> wrote in message news:20652EDD-9EA7-4813-8893-5E51FA72BA1E@microsoft.com... > Is this the correct newsgroup to post Great Plains Report Writer VBA > Customizations questions? Don't see any other GP newsgroups. > > ...

AD synched resources in PWA 2007
Hi, How can we list all the reources which are the object of an AD synch. when we are in PWA 2007? Thanks for help Xiao -- How about looking at the Resource Center page? Or is there something else you are seeking? Let us know. -- Dale A. Howard [MVP] VP of Educational Services msProjectExperts http://www.msprojectexperts.com http://www.projectserverexperts.com "We write the books on Project Server" "Xiao" <nospam@nospam.com> wrote in message news:upAtgcFmKHA.3840@TK2MSFTNGP06.phx.gbl... > Hi, > > How can we list all t...