Posts by [the blue fox]

    Hi all,

    I have been sitting in front of the forums for a while and cant see anything that is helping.

    I have a master file with two sheets. I have another input file with only one sheet.
    Without opening the input file, is it possible to copy the range and paste special values only into a duplicate sheet in the master?

    Re: Delete Entire Row If Cell Equals Qld

    The autofilter line is still highligting yellow.

    Hi Stan
    Thanks .. but this loop seems to take forever. I reduced the 20004 to 65 and it still took AGES to delete the rows.

    Any ideas? I can assume now if I want this done, it will in fact take ages to loop over the 20004 lines?

    Re: Delete Entire Row If Cell Equals Qld

    Hi ..
    I have adjusted to my scenario .. but still not working

    Application.ScreenUpdating = False
        With Sheet21.Range("5:20004").CurrentRegion
            .AutoFilter field:=18, Criteria1:="<>NSW"
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.delete
        End With
        Sheet21.AutoFilterMode = False
        Application.ScreenUpdating = True

    The AutoFilter line is what highlights yellow and preventing it going any further.
    I even left the range as "A5" but still not working.

    Any ideas?

    Morning all.

    I have tried to make head and tails of a few searches to delete entire rows, but I cant seem to adjust the coding to make it work for my scenario.

    I have a row range 5:20004. In that row range column R may have 'QLD' in the cell.
    I am trying to delete all rows within that range that have QLD in column R or delete the row if R <> 'NSW' (would be a better way just in case there is something other than QLD)

    I would love to do this on opening the file automatically.

    Can anyone offer up some help?

    Re: Multiple Formulas Causing File Size Increase

    I use the IF function as if there is no data on the cell in the officer's workbook, then it gives a 00 Jan 00 in the cell that is date formatted or a 0 if the cell is general.

    I use the if, so that if the officer cell is blank, then it too will return a blank value.

    I dont see how it can give a blank cell?

    Re: Multiple Formulas Causing File Size Increase

    It will be periodical that the master file will be accessed, but at least once a day.
    The master has one sheet only, all four workbooks get put onto that.

    I would prefer not to have the master need to open the 4 files to copy the data to.

    And the only drama I have is that B5 on the officer will not be B5 on the master.

    If the copy was able to be done, the copy would need to put them on the newest line of course, so when the sort happens, it will not mess any of the other coloumsn from previous lines.

    I have tried to water my sheets down to be able to send them.
    I hope this is still going to help as 48kb is a very small file.

    Morning all.

    I am working with 4 seperate workbooks where 4 different people enter data into cells.

    I have a 5th workbook that needs to have all the data from the 4 individual workbooks bought across so I can see all data. Like a master file.

    basically, in each cell of the master, I have the following code.

    =IF('[loans - 0708 - officer 01.xls]07-08'!B5="","",'[loans - 0708 - officer 01.xls]07-08'!B5)

    I then have the code for each officer.
    As there are 24000 lines collectively for the financial year, the size of my master is 37mb, even when its empty, obviously cause each cell still have a formular in it.

    Is there ANY way to copy across the data from other workbooks without having to have every single correpsonding cell have this kind of formular to view the code? Also, copy it without having to open the other workbooks.

    HELP !!


    Re: Show Custom Error Message When No Data to Paste

    Sorry Dave,
    Sometimes I can't get my head around the coding, so sorry if I have appeared ignorant trying to understand it.

    I hadn't used that function before, but was able to remove it so as my sheet can be protected and unprotected.

    Thanks for you help .. it works great now :)

    Re: Show Custom Error Message When No Data to Paste

    thankyou so much .. works perfectly.

    I guess there is no chance of the clipboard not being cleared?

    For example.
    If the sheet it protected .. and I have something sitting on the clipboard, when I go to unprotect it manually or via the macro, it clears the clipboard and I have to go grab the data again.

    Any way around this to be built into this coding?

    Morning all.

    A pickle that I can not work out how to do, and I know it's gonna be one of those simple ones.

    Below is a macro I recorded to help copy 'Paste Special - values only' data between two spreadsheets that have different colour and conditional formatting in cells.

    However, if this is run and there is no data to paste, it throws up the End and Debug error.

    Any chance that someone knows that if there is no data sitting on the Clipboard, then a msg box would appear saying "Please select the data you require to be copied" in the message box.

    HELP! .. thanks heaps


    Hi all,

    I need some help on a sorting macro.

    I have a range C11:O104 I would like to sort Ascending first from Column E then Column D

    Only problem is that all cells have formulas so when this macros is run, I get blank cells sorting as well and they end up at the top.

    I have tried to remove blank cells, but I do not want to delete the rows OR the formulas from the cells.

    Can anyone help sort my range with blank cells that contain formulas?


    Good morning all.

    Not sure where to look for help on this issue as no matter what I type in to help files, nothing seems to pop up.

    I have a statistics page in a file that captures data from 12 data sheets.
    I want to be able to look for a value over the entire 12 sheets.

    Column D on all 12 sheets have dates.
    I would like to return a count if any cell in column D across the 12 sheets has a specific value.

    ie .. how many cells have a value of 1 Sept.

    Any ideas?
    Have tried VLOOKUP and COUNTIF .. but cant seem to get them to work across multiple sheets.

    HELP !!

    Re: Clear Contents, Copy And Sort

    Im sorry to say your reply has confused me.

    Prior to copying the data from 01 C onto the master sheet (Consumer), I need all rows in '01 C' in the range B5:G1004 that do NOT have a value to be cleared of their contents so they are blank cells.

    This is part of the code I have to do all 10 sheets.

    and so on until sheet 10.