VB Code For Saving

Joined
Feb 16, 2002
Messages
38
Hello I Have A Button On My Excel Template I Need To VB Code To Do The Following

>Save The Template With The New Data Added On It To An Excel Workbook

>Every 250 Times This Is Done On The Template Have A Message Box Saying A Customised Message.

Please Can You Help, I have 2 Weeks To Do This And Much More So I Don't Have Time To Learn Visual Basic Thank You.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
>Save The Template With The New Data Added On It To An Excel Workbook
Do you want to save the existing workbook with changes or save the worksheet to a new file? If you want to save the worksheet to a new file, do you have a specific file name in mind or do you want to prompt the user for a file name?


_________________

Barrie Davidson
My Excel Web Page
This message was edited by Barrie Davidson on 2002-03-09 12:07
 
Upvote 0
You can try this code:
<pre>
Sub SaveMacro()
' Written by Barrie Davidson

'Variable for the new file name
Dim NewFileName As Variant

'Get the name of the new file
NewFileName = Application.GetSaveAsFilename _
(filefilter:="Microsoft Excel files (*.xls),*.xls")

'Exit the sub if the user selected cancel
If NewFileName = False Then Exit Sub

'Copy the active sheet, save it
'using the new file name, and then
'close it
ActiveSheet.Copy
ActiveWorkbook.SaveAs FileName:=NewFileName, _
FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close

'Increase the counter by 1
Sheets("Counter").Range("A1").Value = _
Sheets("Counter").Range("A1").Value + 1

'Display the message box if the value
'of the counter is >= 250
If Sheets("Counter").Range("A1").Value >= 250 Then
MsgBox prompt:="Your custom message"
End If
End Sub
</pre>

Regards,
 
Upvote 0
Arash, don't take this the wrong way, I don't mean to sound patronising or condescending :).

If this part of your A-Level is to be examined (ie not just course-work), it'd be a good idea to go through Barry's code in the VBA editor and press F1 for help on any of the code statements you're not sure of.

I came unstuck during my Computer Studies O-Level (showing my age there), when I used a piece of code from a friend in my program and couldn't explain what it meant. I ended up being docked 10% for my project...
 
Upvote 0
On 2002-03-09 12:47, Mudface wrote:
Arash, don't take this the wrong way, I don't mean to sound patronising or condescending :).

If this part of your A-Level is to be examined (ie not just course-work), it'd be a good idea to go through Barry's code in the VBA editor and press F1 for help on any of the code statements you're not sure of.

I came unstuck during my Computer Studies O-Level (showing my age there), when I used a piece of code from a friend in my program and couldn't explain what it meant. I ended up being docked 10% for my project...

That's a really good point Mudface.

Took a look at your profile - your rugby interest now explains the moniker :) I've never played the game (actually don't even understand it), but it sure looks like a walk on the wild side :oops:

Take care MudFace and keep up the great work on the board. :cool:
 
Upvote 0
Hi, There Is One Problem With When I Save It, This Error Message Comes Up Run Time Error '9' Subscript Out Of Range. I Go to Debug And This This Is Highlighted In Yellow


Sheets("Counter").Range("A1").Value = _
Sheets("Counter").Range("A1").Value + 1

Could You Help ? Thanks
Arash
 
Upvote 0
I don't Mind If I Get An 'E' For This Project, I just want it done so then I can start revising On my History And Other Subjects, I'll Pull My Exam Grade On This For the Theory, I would learn VB if I had The Time
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top