Posts by MisterT

    Re: A macro to insert a module?

    Thanks Roy,

    I guessed it wouldn't (or shouldn't!) be simple.

    Obviously it would be an unprotected workbook, but in practice, is it easier to generate a message box with instructions and the code to copy?


    Is it possible to (simply) get a macro (in an add-in) to insert a module into the active workbook, and then paste code in?

    I realise this may be open to abuse, so may not be possible, but I am trying to use it to "idiot-proof" the macro inserting process...

    Re: Define range based on xlDown

    Yes, I need to define it as it will be referenced as a named range in another macro to be used later.

    I am happy for it to overwrite an existing range of the same name, as the data grows.

    Re: Define range based on xlDown

    Thanks for your speedy replies,

    This is actually part of a bigger macro, and I need to do this in VBA. I understand the offset method of dynamic ranges in excel, but just need to know the VBA syntax.


    Quick question:

    How can I define a range and make it dynamic?
    I want to define it based on the selection from the active cell down, using xlDown? (I don't know how large the range will be each time)

    ActiveWorkbook.Names.Add Name:="Whatever", RefersToR1C1:= _

    Effectively, I want to make the references R14C6:R15C6 be Activecell:Selection.xlDown.Select

    Any assistance on syntax appreciated!

    Re: Calculation help

    For clarification:

    Is the date and time exactly as shown, i.e. in that exact format of
    "m/dd/yy hh:mm" all in one cell.

    I assume this is data generated from some kind of report, rather than typed in by hand?

    Re: Copy Paste Values Worksheets to New Workbook


    Thanks - For some unknown reason, it works when fired using Alt-F8, but not with the assigned shortcut key (then it only pastes the active sheet) ...

    I will scrub the macros to a text file, re-paste, and see if that fixes it.

    Thanks again,


    EDIT: Found a similar macro in the personal.xls workbook had same shortcut assigned. Works perfect now.


    I'd appreciate a pointer on what argument I'm missing to:

    copy selected (multiple) sheets, then paste values to a new workbook containing those (multiple) sheets:

    Currently, this only pastes the active sheet, not all the selected sheets. Am I missing something very simple?

    Thanks in advance,


    Can anybody tell me what is wrong with this? I must be missing something very, very simple...

    Re: Fixing Colour Palette

    Now it gets wierder...

    I just scrubbed the file down to one sheet and removed all numbers (to reduce file size).

    Now when I open it, my colour palette overrides the one in that sheet, so their "nice" green palette gets converted to mine.

    I think this is an excel rather than a workbook problem. Baffling, and very annoying.

    Sadly the file is still too big to attach. (even removing all formatting and values)


    Every time I open a particular workbook it changes the colour palette to a lurid green for all open workbooks. Even if I then close the offending workbook, all others remain the same disgusting colour.

    Does anybody know how to return to the default colour pallette?

    Thanks in advance


    Re: Italicise and superscript last 3 characters in active cell

    Thanks for the help, Norie.

    The code below now serves my needs very well, although it doesn't handle errors if there is no "(" in the cell. I can live with that.

    Re: Italicise and superscript last 3 characters in active cell

    Thanks again, Norie.

    Attempting to make this flexible enough to deal with double-digit footnotes, I wrote the following, which is not quite right. A correction would be gratefully received!

    Sub Footnote()
    With ActiveCell.Characters(Start:=Len(ActiveCell) - Find("(", ActiveCell) + 1, ).Font
        .FontStyle = "Italic"
        .Superscript = True
    End With
    End Sub