VB Code For Saving

[Arash_The_A_Level_Student]

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.

[Barrie Davidson]

>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 ]

[Arash_The_A_Level_Student]

Hi It's Template And I'd Like It To Save To A New Workbook When New Data Is Added In and I want To Prompt The User For A File Name Thanks.

[Barrie Davidson]

You can try this code:

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

Regards,

[Mudface]

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...

[Barrie Davidson]

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

Take care MudFace and keep up the great work on the board.

[Arash_The_A_Level_Student]

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

[Arash_The_A_Level_Student]

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

[Juan Pablo González]

There should be a sheet named "Counter" for this code to work. Do you have one ?

[Arash_The_A_Level_Student]

No How Do I Do This?