OT: Visual Basic in Excel
Discussion
Hi guys.
Just wondered if any of you know about Visual Basic in Excel. I need to write some code that will save the active work sheet but not the entire wookbook. This will then be attached to a button somewhere on about 70 sheets.
I could do it sheet by sheet using:
Sheets("Sheet Name").Save but that would take feckin ages to write and then put on each sheet.
Anybody know a shortcut?
Cheers.
Matt
Just wondered if any of you know about Visual Basic in Excel. I need to write some code that will save the active work sheet but not the entire wookbook. This will then be attached to a button somewhere on about 70 sheets.
I could do it sheet by sheet using:
Sheets("Sheet Name").Save but that would take feckin ages to write and then put on each sheet.
Anybody know a shortcut?
Cheers.
Matt
Hi Matt
The .Save method/function can only be invoked on a Workbook object as opposed to a Worksheet object, therefore I would be surprised if you could use it to save a specific Worksheet.
However, as it sounds like you have used it successfuly then I would use the Sheets property but specify a numerical index rather than the sheet name, i.e.
Dim intSheetIndex As Integer
intSheetIndex = ActiveSheet.Index
Sheets(intSheetIndex).Save
Where 'n' is the index number of the active Worksheet.
Alternatively you could just read the name of the active worksheet into a String variable and use the following:
Dim strName As String
strName = ActiveSheet.Name
Sheets(strName).Save
If by any chance using .Save does not work try using Syntax 1 of the .SaveAs method which is explained fully in the VBA help files.
If you want to save a number of Worksheets seperately but in the same operation then obviously you can just interate through using the indexes using a For-Next loop after counting the number of sheets in the Workbook or by stepping through the Sheets collection for the Workbook.
Hope this helps.
roadsweeper.
The .Save method/function can only be invoked on a Workbook object as opposed to a Worksheet object, therefore I would be surprised if you could use it to save a specific Worksheet.
However, as it sounds like you have used it successfuly then I would use the Sheets property but specify a numerical index rather than the sheet name, i.e.
Dim intSheetIndex As Integer
intSheetIndex = ActiveSheet.Index
Sheets(intSheetIndex).Save
Where 'n' is the index number of the active Worksheet.
Alternatively you could just read the name of the active worksheet into a String variable and use the following:
Dim strName As String
strName = ActiveSheet.Name
Sheets(strName).Save
If by any chance using .Save does not work try using Syntax 1 of the .SaveAs method which is explained fully in the VBA help files.
If you want to save a number of Worksheets seperately but in the same operation then obviously you can just interate through using the indexes using a For-Next loop after counting the number of sheets in the Workbook or by stepping through the Sheets collection for the Workbook.
Hope this helps.
roadsweeper.
The Chimp is fine, but feeling a little neglected as I am working in London during the week these days. However, it's quite nice when I come home at the weekends as it feels more like a special occasion when I take her out for a drive!
I should be easier to spot these days as I've just bought a new plate. Don't want to post it on here but it will be obvious if you see it. I'll keep my eyes open for a Fiesta flashing it's lights at me furiously!
Yeah, missing Chatsworth was a downer. It was mainly because of work and also that I've just bought a new house and so had loads of stuff to do at home as well. Next time though....
Did the VBA stuff I suggested work OK?
Just noticed that your profile says you are doing an MEng in Automotive Engineering after you've finished your GAP year. That's almost exactly what I did. I took a year out via the Year In Industry (YII) scheme and then did an MEng in Mechanical Engineering at the University of Sheffield and graduated last year. However, I'm now working as a management consultant which has nothing to do with engineering so work that one out!
I like the comment in your profile regarding free pizzas for PistonHeads!!
Is it the dominoes in Macc and if so do they deliver to Poynton? Your boss sounds like a real kn0b - I'd be happy to show him a proper car sometime...
Lee.
I should be easier to spot these days as I've just bought a new plate. Don't want to post it on here but it will be obvious if you see it. I'll keep my eyes open for a Fiesta flashing it's lights at me furiously!
Yeah, missing Chatsworth was a downer. It was mainly because of work and also that I've just bought a new house and so had loads of stuff to do at home as well. Next time though....
Did the VBA stuff I suggested work OK?
Just noticed that your profile says you are doing an MEng in Automotive Engineering after you've finished your GAP year. That's almost exactly what I did. I took a year out via the Year In Industry (YII) scheme and then did an MEng in Mechanical Engineering at the University of Sheffield and graduated last year. However, I'm now working as a management consultant which has nothing to do with engineering so work that one out!
I like the comment in your profile regarding free pizzas for PistonHeads!!
Is it the dominoes in Macc and if so do they deliver to Poynton? Your boss sounds like a real kn0b - I'd be happy to show him a proper car sometime... Lee.
quote:Only managed to have a little play on wednesday and as yet no joy
Did the VBA stuff I suggested work OK?
Not done any today though because of 4 day weekend 
Will keep plugging away though until it works 
quote:Doesn't seem to work. B@stard Microsoft...
Couldn't you just select every sheet, then when you create the function on the top sheet it will then appear on every sheet?
Lee, yep I start the course in September up in Leeds. I'm doing a Year in Industry too! Working in Stretford but not really learning much, just being cheap labour. I seem to have been unlucky with my placement though. Might be being made redudundant soon anyway. Did you have to do that business course down in Pershore? What a sh1thole that place is! Nice being paid to go there though. Had to spend about £250
on beer 
to keep me sane though. I think I'll try and get into motorsport when I've finished uni. Become a race engineer or something like that. As long as it keeps me in TVRs and loose women I don't really care!
Unfortunately I've quit Dominoes now because of the knobhead owner. I closed the shop 30 mins early after working a 14 hour day with no breaks because he couldn't be arsed work his shift and he dropped my wages. I enjoyed quitting though. He wasn't expecting it and it left him up a certain creek!
He also didn't like finding out everyone thats works for him thinks he's a d1ck. 
If you happen to see a silver W reg 3 series being driven like a rental car will you show him what Blackpool's finest are made of please? Nice on mate. CU later.
Matt
The following VBA code should work for you. Code can sit in any workbook, doesn't have to be the model you want to slice. Names for the files come from the sheet tabs. The trick is to copy the sheet to another workbook and save it from there.
Sub CopyAndSaveSheets()
Dim ws As Worksheet
Dim thiswb As Workbook
' In case you have more than one file open this allows Excel to
' keep track of which book it started from
Set thiswb = ActiveWorkbook
For Each ws In thiswb.Worksheets
' Copy the sheet to a new workbook
ws.Copy
' Following is optional - it will remove all formulae on the models so
' you don't get those annoying link messages when you reopen it
' ActiveWorkbook.Worksheets(1).UsedRange.Value = ActiveWorkbook.Worksheets(1).UsedRange.Value
' Save the sheet with the name on the tab
ActiveWorkbook.SaveAs ActiveWorkbook.Worksheets(1).Name
' Close the new book, delete if you want them left in memory
ActiveWorkbook.Close
Next ws
End Sub
Give me a shout if it doesn't work, I tested it on a few here and it seems OK for me.
Sub CopyAndSaveSheets()
Dim ws As Worksheet
Dim thiswb As Workbook
' In case you have more than one file open this allows Excel to
' keep track of which book it started from
Set thiswb = ActiveWorkbook
For Each ws In thiswb.Worksheets
' Copy the sheet to a new workbook
ws.Copy
' Following is optional - it will remove all formulae on the models so
' you don't get those annoying link messages when you reopen it
' ActiveWorkbook.Worksheets(1).UsedRange.Value = ActiveWorkbook.Worksheets(1).UsedRange.Value
' Save the sheet with the name on the tab
ActiveWorkbook.SaveAs ActiveWorkbook.Worksheets(1).Name
' Close the new book, delete if you want them left in memory
ActiveWorkbook.Close
Next ws
End Sub
Give me a shout if it doesn't work, I tested it on a few here and it seems OK for me.
Gassing Station | General Gassing [Archive] | Top of Page | What's New | My Stuff