Posts by yjoshi

    Hi This can be certainly done.. no probs.

    I have worked with lot of these before. If you can give a few more pointers to the exact problems, it will be really great.

    such as what is the structure of data, how many columns, how many sheets,
    do you want to just dump the data one below the other or something else ... etch.

    See following code which have used to consolidate data on one sheet from different sheets in the same file.


    I think you can increas performace of your code by this way

    Sub CopyTest1()
       Range("A1:H18").Copy Destination:=Range("A65536").End(xlUp).Offset(7, 0)
    End Sub

    Also, I tested the code and is working fine even with formulae.

    Can you tell what is the problem with formulae?

    Hi Xlite,

    I think your query is not very clear.

    What do you mean by "how do you reference a solid path..."

    I thought you wanted... how do you reference to a cell in another worksheet as a formula ....
    If you want that... following is the code.... , else please clarify.

    ActiveCell.FormulaR1C1 = "=Sheet2!RC"


    Very simple

    Format -> conditional Formatting -> Condition
    Cell Value is -> greater than -> 200

    Click on Format

    Choose the formatting

    Say OK.

    Hope this helps.


    Try following code:

    The difference from Derk's approach is that i am using Me keyword which will mean, the sheet in which the code resides will be used.

    Also looks like you want to lock only the specific range and not all the cells, so i have put the code for locking, hiding formulae in the same with clause.

    It did work for me perfectly.


    Ho Wes,

    The post is a bit ambiguous!! Not really sure if you want to remove all filters on all sheets or do something special if there is a filter.

    Following code will make sure that autofilter for all sheets is set to false, so that your macro can run and will not be affected by the filters.

    Sub SeeFilter()
    Dim w As Worksheet
    For Each w In Worksheets
        w.AutoFilterMode = False
    Next w
    End Sub

    If you post what you are doing in your code, we can suggest some better way!!

    Hi Les,

    I could use the SAME code to hide the range. It worked.

    Just make sure that your sheet is not protected.

    Also, as per your requirements, you do not want user to enter anywhere except where you want... why don't you try to unlock the entry fields and then protect?

    Hope i am not hitting the wrong nail :(

    Yet Another Approach.... ( I know there can be plenty more)

    I am using union of special cells containing constants or formulae (which will exclude the formatted blank cells) and then getting the maximum row and column, adding 1 to row instead of offset.

    But i think, because of the for loop, it may not be extremely time efficient....

    If you are DEAD-SURE that there are no Blank Cells with formatting, then you can use


    However this will fail, if there is a blank cell outside the text-entered bloks, with some formatting enabled.

    HTH (Hope this helps)

    Hi Weasel,
    In your code, you are using exactly same if function twise, which can also be avoided.
    Also, the selection should be inside if loop, as, if the sheed does not have any entry then the code will error out.

    I have made these 2 changes...

    Will try to see if i can find any better way...

    Following code will give you your desired output.

    The only change fron the previous solution is that, there is only one Named Rage "Start" and there is no command button. The stop watch starts as soon as the user form is initialized (in our case with a control button from the worksheet.

    Hope this helps

    Let me give it a try based on the minimum requirement you have written.

    This can be used as a starting point and you can improve on the tool or ask for further help.

    1. On Sheet1 enter 4 headings in A1 to D1 "Number, Start, End, Time"
    2. Create three Named Cells (Insert -> Name -> Define) anywhere in the workbook. "Started, Number, Start" (which will be used for storting temp. values)
    3. Create a user form in VBE ( Ctrl + F11 to see VBE and then insert -> userform)
    4. Create one Text box and one command button on the user form.
    5. Paste following code in the code window for user form.

    This should help you record the timings for different players (numbers).

    If this is fine with you, we can work on getting the fastest etc.


    I will strongly suggest use of Text Box instead of merved cells in case you want to write lot of text. This can help you in formatting the things better. Also, you can make the background as No Fill, so that you don't see a white box.

    Also, the Row Numbers are a bit confusing. You are mentioning Row E ... is this right, ro you mean column E ?

    Worksheets(1) does the processing for first worksheet in the workbook.

    If you want to protect all your sheets, you can put it in For loop or do the processing on specific sheet with
    follwoing code, where "Protected_Sheet" is the name of the sheet on which the processing is needed.

    Paste this in cell "C3"



    1. B3 or B2 is "X" (Cell besides or above the cell besides)
    2. the two cells above the result cells are not equal

    Result = 0


    end if

    Hope this helps, if not, please clarify the conditions... cheers :)

    Hi Byron,

    Welcome to OzGrid!!!

    Please help us to help you.

    The question is a bit unclear.
    Can you let us know what do you mean by "pull correlating 7 columns"?

    Also what should happen if we have 4-H more than once... how should we pull / combine the data.

    At times it happens in first few posts, that you may be a bit unclear... but do not refrain from posting... we are as eager to help you as you are to get the help :)


    I am putting a SIMPLE code to transfer data from Sheet1 to the next blank row in Sheet2.
    You can add other flavours like screen updation, selection after paste etc etc.

    Just open a blank workbook, paste this code in sheet1 and run it a few times by entering data in sheet1 and selecting the same.

    Sub transfer()
    Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End Sub

    I have taken 4-5 steps in one go.
    1. selecting sheet2
    2. going to the last cell in A col (A65536)
    3. then going up to find last used cell in A col
    4. go to next cell
    5. paste.

    Easy ?? :cool:

    Following code should solve your problems.

    Not really sure why you used paste special... but you can still replace the paste command with your old command, if you need, and it also works fine.

    This will do the trick

    If you want it for more worksheets, put it in for loop

    Private Sub Workbook_Open()
    With Worksheets(1)
        .EnableSelection = xlUnlockedCells
        .Protect Contents:=True, UserInterfaceOnly:=True
    End With
    End Sub