[ms office - 2007] Excel Macro - Save values individually

[Duall]

Hello! So, I have an issue that I have not been able to resolve, I feel it would be better phrased with the whole scenario.

 

The employees at my company must fill out 8-12 predefined spreadsheets every time they make a sale. They all have the same information, so I figure I'd make it more efficient and make a "master" sheet to enter the information, and then it would go to all the correct fields. Really easy with "=Master!D32" etc etc. However, then they told me they had to save them individually and send them individually.

 

From my understanding, this requires two seperate components: saving the values to the sheet, not the formulas, and also to save each sheet individually. Add to this the desire for them to only hit buttons. So, the employees would like a button on the master sheet to save one sheet in this way, as opposed to saving them all.

 

For instance, They enter all the information and need to save the sheet entitled "IP", "PS" and "MP", which are acronyms for different companies we use if you were curious. So on the master sheet, they click the corresponding buttons, and are brought a save screen, where they rename the file into whatever they so choose, and it saves just the sheet with all the correct values.

 

I have googled both solutions (sans the button), but I've run into a problem. I don't understand macros in the slightest, so cannot tweak the scripts I've found to suit my cause. Also, there's no explanations that the people give. Could anyone throw a possible solution with an explanation attatched at me? It's an interesting problem I have never come across, and am curious to know what others think about it.

 

Thanks.

[Duall]

I managed to find a website that catered (mostly) to my needs. The command buttons turned out to be not difficult, and the remainder of my needs were solved through interpretation of this website. For those that want to know the answer, here it is:

 

Option Explicit

 

Sub doubleTap()

Dim NewName As String

Dim nm As Name

Dim ws As Worksheet

 

Sheet2.Unprotect Password:="secret"

 

 

If MsgBox("This will make a new file in the same directory" & vbCr & _

"Do you want to make a Double Tap Quote?" _

, vbYesNo, "Save Double Tap Quote") = vbNo Then GoTo TotalEnd

 

With Application

.ScreenUpdating = False

 

' Copy specific sheets

' *SET THE SHEET NAMES TO COPY BELOW*

' Array("Sheet Name", "Another sheet name", "And Another"))

' Sheet names go inside quotes, seperated by commas

On Error GoTo ErrCatcher

Sheets(Array("DT")).Copy

On Error GoTo 0

 

' Paste sheets as values

' Remove External Links, Hperlinks and hard-code formulas

' Make sure A1 is selected on all sheets

For Each ws In ActiveWorkbook.Worksheets

ws.Cells.Copy

ws.[A1].PasteSpecial Paste:=xlValues

Application.CutCopyMode = False

Cells(1, 1).Select

ws.Activate

Next ws

Cells(1, 1).Select

 

' Remove named ranges

For Each nm In ActiveWorkbook.Names

nm.Delete

Next nm

 

' Input box to name new file

NewName = InputBox("Please enter the name of your quote", "File Name")

 

' Save it with the NewName and in the same directory as original

ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xlsx"

ActiveWorkbook.Close SaveChanges:=False

 

.ScreenUpdating = True

 

 

 

 

End With

 

TotalEnd:

Sheet2.Protect Password:="secret"

 

Exit Sub

 

ErrCatcher:

MsgBox "Specified sheets do not exist within this workbook"

Sheet2.Protect Password:="secret"

End Sub

 

This is the script I'm using. I have a button for each of the pages (IP, PS, MP, etc) and all that changes is the wording on titles and dialog boxes and the sheet reference (sheet2, sheet3, etc). This is essentially the entire script from the first website linked, aside from using four tweaks. I entered a password protection (unprotect/protect) to three places, made a goto tag (is that what they're called?) and had the first option dialog box go to that instead of "exit sub", changed the wording, and then finally I changed the "xls" format to a "xlsx", because we run 2007. If I left it as "xlx", it said it was saved in a format which wasn't correct if you opened it. As for command boxes, there is a good runthrough here.

 

Now that I've fixed the problem, I wonder if I can make it more efficient. As far as scripting, I can find an exsisting script and mish-mash it with other scripts, using logic and kind of guess and check to see if it worked. I'll need to look up more stuff for this, but here's my new idea: Use checkboxes to denote the quotes the user wants to save, and have a single button to save them all. The naming scheme could throw the quote type (IP, DT, etc) in front of it so if you had entered "100" for the file name and checked the "IP", "DT", and "PS" boxes it would save "IP100", "DT100", and "PS100" when you clicked the single save button. So this would involve linking checkboxes into a macro (don't know how to add new objects really), and executing the code aligned with that sheet every time.

 

I don't know programming, but I know concepts. My assumption is that this is essentially what would have to happen. When save is clicked, it checks the first checkbox. If so, it does a goto to that section of code and then unchecks it (so it doesn't do it again and again and again), if not it skips it. Then it would go to the next and so on and so forth, until it hit the end, which would "exit sub" with all of the goto tags after it. Does that sound logical?

 

Thanks.

Edited August 20, 2010 by Duall
Forgot a link for the command boxes