Posts by pradeep_atm

    Re: copying a protected formula


    Hi,


    To protect your formula's from accidental changes, you can look at the following options:


    1. You can protect the sheet and hide only the formulas(Format Cells - Protection - Hidden) - this allowes you to copy the formula and paste it in the same sheet(unless you lock the cells)


    2. The second one would be to Name your formula.(Go to Insert Menu- Name- Define). In the Refers to bar, type your formula and give a name to this formula in "Names in Workbook" bar.


    And you can type this formula in a cell prefixed by "=" to get the desired out put. These named formula can be copied like your normal formula.


    The second option is a better one.


    HTH.

    Re: Set printarea for a dynamic range of cells


    Reboot,


    Welcome to Ozgrid Forum.


    Try the following code, though there may be an efficient way of coding it..


    [vba]
    Dim rn As Range
    Set rn = Selection
    ActiveSheet.PageSetup.PrintArea = rn.Address
    [/vba]


    Viper, Reboot was asking for PrintArea and not Print...


    HTH.

    Re: copying a protected formula


    Hi,


    I am not sure as to what you trying to achieve. In a protected sheet(with all the cells in locked mode), one can't paste anything(that is, one can't change the contents of the cells), although one can copy it, to paste it elsewhere.


    Post back, as to why you want this, so that someone can help you.


    HTH.

    Re: Controlling paste method


    Hi Rana,


    Welcome to Ozgrid Forum.


    Does Paste special option will not work for you...(Go to Edit - Paste special). Paste special gives you lot of options to chose. You can select value option, to paste values only.


    HTH.

    Re: copying a protected formula


    Hi tgawalid,


    Welcome to Ozgrid Forum.


    To achieve what you wanted in your post, just try the following:


    1. Select the entire sheet and go to Format cells-Protection tab-un check lock option.


    2. go to Format Cell option for the cell in which you have formula
    and go to Protection tab and Check the Lock option


    And now protect the worksheet.


    Now you can copy the formula and have it pasted any where.


    Hope this helps.

    Hi All,


    I have a file which has data pertaining to diffrent countries and for different years. Now I need to send the data to the respective countries, for which I should create new file copying the data pertaining to that country.


    Therefore I need to achieve the following:


    1. Copying the range for each country and saving at as a new file(file name being the country name)
    2. For all these Col A should be constant(as this being the products name..)
    3. The data to be copied is constant range..that is each coutry has 8 columns..except two countries which I can do manually..



    I am enclosing the file for your reference.


    Any guidance on this greatly appreciated...


    thanx

    Re: CC to more than one person


    Hi Norie,


    It was that simple....!


    thanx for the solution..it worked a treat. I was looking at the .CC property as that it will take as many as one put(unlike .To).


    Thanx

    Hi All,


    I have taken the VB code from http://www.rondebruin.nl site to send mail to a list of persons in a range.


    Whereas this coding will not allow me to CC to more than one person. Is there any way that I copy to more than one person.


    This is what I tried, but it will only take the latest value(i.e cell.offset(0,5).value)...


    With OutMail
    .To = cell.Value
    .CC = cell.Offset(0, 3).Value
    .CC = cell.Offset(0, 4).Value
    .CC = cell.Offset(0, 5).Value
    .Subject = cell.Offset(0, 8).Value
    end with


    I wanted to use code tags....but how to use it..



    Thanx
    Pradeep

    Re: copying info


    Hi LeeGlover,


    Welcome to Ozgrid Forum.


    Well can you make it little more clear as to what you want to achieve.


    If you trying to link to a cell in another sheet(say Sheet2), then you can enter "=Sheet2!A1", to get the contents of cell A1 of sheet2 in any cell where you enter this formula in sheet1.


    Hope this helps..


    thanx

    Re: Listbox Populating


    Hi Andy,


    Apologies for the delay in replying, infact I was awaiting e-mail notification.


    Regarding your comprehensive solution...what do I say...U are a GURU..(I wish i would become one)......It just works the way I wanted..!!! :rock:


    And I assigned it to a key board short cut(by way of a macro in my personal workbook(hidden)) and have the list displayed with this key board short cut wherever I want......Delighted...


    And this should be useful to many other who are working on workbooks with umpteen sheets....


    Keven, thanx to u too..


    Thanx a ton!
    Pradeep

    Re: Listbox Populating


    Hi Kevin,


    Thanx for the solution. It worked.


    A small problem..is it possible to use key board movement as the selection mode instead of doing mouse click.


    And one more point here is that i got lot more hidden sheets which appear early in the list by the time key stroke goes there it throws up some error message there...any way to get over this..


    thanx
    Pradeep

    Re: Listbox Populating


    Hi Kevin,


    thanx for your reply.


    It seems I have not made my point clear in my post.


    What I wanted is:
    When I select a sheet name in the list box that particular sheet should get activated.


    This utility will be the same as the mouse "right click" option on the arrow marks to the left side of sheet tab. And the only difference here would be ere I can have the list displayed with a key board short cut and I need not use mouse(which makes the job faster).


    thanx
    Pradeep

    Re: Listbox Populating


    Hi Andy,


    This seems to be a good tool getting displayed all the worksheets in a work book.


    While I can do upto the displaying stage, could you please guide as to how when I select a particular sheet that particular sheet gets activated.


    Please help..



    thanx
    Pradeep

    Re: nested if or and syntax


    Hi Will R,


    You are correct. Thanx for letting me know that..


    would be glad if you could guide on bulk mailing..I have tried from other sites but it not seem to work. Albeit I know that this is not the right forum to ask this question, if I place this in Excel/outlook forum it's unlikely that any one reply to that.


    Thanx
    Pradeep

    Re: Gridlines & Print Footer


    Hi MisterT,


    thanx for the reply.


    Regarding my query on Gridlines it worked and thanx for that. Can you please explain what below line your code do:


    wsSheet.Select Replace:=False


    Regarding my query on Print footer, I wanted something which happens by defualt, instead of me running the macro every time before I go for a print...


    Please help with this, if possible..


    thanx for your reply..

    Hi All,


    I have two small questions:


    1. Is it possible to take off the Gridlines in the entire work book, as against in each worksheet


    2. Is it possible to set the Footer of the type "Page 1 of ?" In Page set up as the default set up. That is I don't want to change the footer every time I take a print to "Page 1 of ?" mode, I want to make it a default option.


    Please help.


    Thanx
    Pradeep

    Re: Use Conditional Formatting formula to check if cell is blank?


    Hi,


    In the formula box use the formula as: = B1="" and do not touch the format button(so that the color will be nothing if B1 has nothing). You have to apply this formating to cell A1(Format - Conditional Formating -Formula Is(in Condition 1)).


    Hope this helps.


    thanx
    PRadeep