+ Post New Thread
Results 1 to 2 of 2
Office Software Thread, Excel Macro - Save values individually in Technical; Hello! So, I have an issue that I have not been able to resolve, I feel it would be better ...
  1. #1
    Duall's Avatar
    Join Date
    Aug 2010
    Location
    Sarasota
    Posts
    8
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Excel Macro - Save values individually

    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.

  2. #2
    Duall's Avatar
    Join Date
    Aug 2010
    Location
    Sarasota
    Posts
    8
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.
    Last edited by Duall; 20th August 2010 at 04:52 PM. Reason: Forgot a link for the command boxes

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2007] Excel refuses to save file after encrypting
    By TheCrust in forum Office Software
    Replies: 3
    Last Post: 20th November 2010, 10:01 AM
  2. Macro + Excel 2007 + SIMS
    By SteveB in forum MIS Systems
    Replies: 20
    Last Post: 3rd March 2010, 09:23 AM
  3. [MS Office - XP] Excel Macro - If Cell Value
    By ahuxham in forum Office Software
    Replies: 0
    Last Post: 18th December 2009, 10:27 AM
  4. help with excel macro needed
    By oyoung in forum Windows
    Replies: 0
    Last Post: 30th March 2007, 10:27 AM
  5. Excel Macro - Edexcel GCE Exams
    By steve in forum Educational Software
    Replies: 0
    Last Post: 16th May 2006, 02:49 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •