Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Input on where to placemacros (Excel 20002)

    I have created a spreadsheet and would like to use it as a "template" for other spreadsheets. I recorded a macro to saveas "ExcelTmp.xls." The problem is, everytime I play the macro, it prompts me that there is an existing file, should I overwrite or not. Is there a way eliminate the prompt? I don't want it to ask if I want to overwrite the file, just overwrite w/o asking, LOL.

    Also, when I open the saved spreadsheet, it also copied the macro. I don't want the macro in the spreadsheets, only in the "master template." How do I fix this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Input on where to placemacros (Excel 20002)

    The way Excel treats templates is different from Word. When you create a new workbook based on a template, the entire template is copied, including macros. If you want to make macros available without copying them to all workbooks, you can use several methods, depending on your needs:
    <UL><LI>If the macros are for your personal use only, store them in your personal macro workbook Personal.xls. See Legare Coleman's <!post=Personal.xls Tutorial,118382>Personal.xls Tutorial<!/post>.
    <LI>If the macros are meant to be used by several persons, you can store them in an Excel add-in (.xla).[/list]If you want to use a workbook as a template, save it as such - Excel templates have an .xlt extension. If you create a workbook, select Save As... and select Template (*.xlt) as file type, Excel will automatically switch to your Templates folder. You can create a new workbook based on a template by selecting File/New...

    I don't understand why you want to use a macro to overwrite the same file all the time. But you can suppress the prompt by setting Application.DisplayAlerts = False before the SaveAs instruction. You should set Application.DisplayAlerts = True immediatley afterwards to restore the normal behavior.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input on where to placemacros (Excel 20002)

    Thank you very much Hans, great information. As you can tell, I am not very familiar w/ Excel. I created a template and made the modifications to the macro and everything is working great.

    I have another macro question if you don't mind. I was able to record parts of it but need help on looping. Here's what I need, the formula goes in cell "S*":

    If "S2" is blank AND "R2" = "Y", "S2" = "No", else leave the cell blank.

    I dont know how to check if "S*" is blank and make it loop to the next row until no value is found on "R" - meaning that's the last row with information.

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Input on where to placemacros (Excel 20002)

    Assuming that you have a contiguous block of cells in column R (no gaps), starting at cell R2, you can use the code equivalent of the End key followed by the arrow down key to find the last filled cell in the block. The code to fill cells in column S could look like this:

    ' Declare two variables
    ' oRange is the block in column R
    ' oCell is a cell in this block
    Dim oRange As Range, oCell As Range
    ' Start with cell R2
    Set oCell = ActiveSheet.Range("R2")
    ' Get the contiguous block
    Set oRange = ActiveSheet.Range(oCell, oCell.End(xlDown))
    ' Loop through the block
    For Each oCell In oRange
    ' Test condition; Offset gets the cell in column S
    If oCell = "Y" And oCell.Offset(0, 1) = "" Then
    ' Set value in column S
    oCell.Offset(0, 1) = "No"
    End If
    Next oCell

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input on where to placemacros (Excel 20002)

    Thank you, thank you, thank! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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