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

I can not get the file size down to anywhere near 48kbs.
I have to retain some of the coding needed to show, but the file if too big.

Sorry
I may have to leave this one.

Re: Delete Entire Row If Cell Equals Qld

StephenR
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?
Thanks

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.

Re: Multiple Formulas Causing File Size Increase

Apologies for the misuse of [ code ]

I am afraid I dont understand your response though

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 !!

Tayler

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

Sub data_input()
    
    ActiveSheet.Unprotect
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B5:AG4804").Select
    Selection.sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("B5").Select
    ActiveSheet.Protect
    
End Sub

Tayler

Re: Open File In Next Blank Cell

Thankyou VERY much .. works perfectly.

Re: Open File In Next Blank Cell

sorry to be a pain, but that last line selects the first cell under the pane and not the next one that is blank.

any ideas?

Re: Open File In Next Blank Cell

HI ..

That's unreal. Seems easy and works like a charm.
It puts the row i want where it should be, but not where the cursor is for input.

Any chance you would know the code to select that blank cell so they can open and just begin inputing?

Thanks

Morning all.

Was wondering .. as I can not seem to find the right type of coding.

I am trying to have the file open in the next blank cell on column C, ut at least have 5 lines showing above it as there is Freeze Panes.

Is this possible for excel to do upon opening?

Thanks

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?

HELP!
Tayler

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

*shrugs*

Thanks for your help .. though I am not quite getting what I am trying to achieve. Not sure how to word what I am after.

Thanks for trying

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.

Sheets("01 C").Select
    Selection.Copy
    Sheets("Consumer").Select
    Rows("5:1004").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Transpose:=False
    Rows("5:1004").Select


    Sheets("02 C").Select
    Selection.Copy
    Sheets("Consumer").Select
    Rows("5:1004").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Transpose:=False
    Rows("1005:2004").Select

and so on until sheet 10.