Posts by lpop80

    Hi,


    I'm trying to write a macro to autofilter two sheets in a workbook.


    The first filter is based on a value in a dropdown list. Within the dropdown list, there are a series of 4 digit codes some of which begin with a zero (e.g. 0112, 4568, 9999, 0765, etc).


    The second filter is in another sheet and it's simply trying to filter out all values that aren't zero.


    Here's what I have so far but it's not working.....


    Code
    1. Dim CostCentre As Integer
    2. CostCentre = Worksheets("Instructions").Range("A6").Value
    3. Sheets("Sheet1").Select
    4. ActiveSheet.Range("$A$1:$AS$5000").AutoFilter Field:=1, Criteria1:=CostCentre, _
    5. Operator:=xlAnd
    6. Sheets("Sheet2").Select
    7. ActiveSheet.Range("$A$1:$AN$85").AutoFilter Field:=40, Criteria1:="<>0", Operator:= _
    8. xlFilterValues
    9. End Sub



    Thanks!

    Hello,


    I'm trying to add formulas into columns K:M


    The person using this will fill in columns A:E, G:I is easy to populate then but what I'm struggling with is the formulas in K:M


    Total hours in K is easy enough - It's just End time (I) less start time (G) less any minutes someone was late back from break (H). The only bit I'm struggling with is that end time is generally after midnight and start time before.


    It's the night shift formula in M I'm really struggling with - I want the total hours worked between 00:00 and 04:00 which is classed as night shift.


    Standard hours in L then is just K-M


    Thanks in advance!


    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 1119"]

    [tr]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [td]

    F

    [/td]


    [td]

    G

    [/td]


    [td]

    H

    [/td]


    [td]

    I

    [/td]


    [td]

    J

    [/td]


    [td]

    K

    [/td]


    [td]

    L

    [/td]


    [td]

    M

    [/td]


    [/tr]


    [tr]


    [td]

    Start Hrs

    [/td]


    [td]

    Start Mins

    [/td]


    [td]

    Late from Break 1

    [/td]


    [td]

    End Hrs

    [/td]


    [td]

    End Mins

    [/td]


    [td][/td]


    [td]

    Start time

    [/td]


    [td]

    Late Break 1

    [/td]


    [td]

    End Time

    [/td]


    [td][/td]


    [td]

    Total Hrs

    [/td]


    [td]

    Standard Hrs

    [/td]


    [td]

    Night Shift

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]19[/TD]
    [TD="align: right"]46[/TD]
    [TD="align: right"]5[/TD]
    [TD="align: right"]6[/TD]
    [TD="align: right"]1[/TD]

    [td][/td]


    [TD="align: right"]19:46[/TD]
    [TD="align: right"]00:05[/TD]
    [TD="align: right"]06:01[/TD]

    [td][/td]


    [TD="align: right"]10:10[/TD]
    [TD="align: right"]06:10[/TD]
    [TD="align: right"]04:00[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    The formula works if entered into the worksheet, it returns the cell reference I wanted.


    What code would I use though to select that cell? E.g. if the formula returned cell N86, I want to select that cell using code in a macro.


    Would it be something like


    Sheets("Production data").").CellsXXXXXXXXX.Select


    Thanks

    Perfect, thanks very much!


    One last thing, what code would I use to move to the last instance of a line number in column N? For example, in the production data tab if I wanted to select the last instance of line 4, I'd select cell N69. For the last instance of line 5, I'd select cell N86.


    Thanks

    Thanks but I may be missing something here. When I run the code, I get an error and only machine 1 has updated.


    I've attached an updated file, hopefully this will explain what I'm trying to achieve a bit better. There is an extended range of data, for lines (I realised the live data has "line" and not "machine") 1-6 yet there are only data tabs for lines 1, 2, 3 and 6. Therefore I want the code to copy the data to the relevant tab if there is a tab, but if there isn't a tab ignore the data for that line.


    Does that make sense?


    Thanks.

    Thanks nyatiaju, I keep getting an error when I run the code where this part is highlighted in yellow......


    Set LineSheet = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets.Count)


    I think it's because there are other tabs in the workbook with different names perhaps? Also, in column N of the production data there are machines 1-18 but I may not want the data for every machine copied to a tab. Do you know how to tweak the code so if there is not a tab such as "Machine 1", "Machine 2", "Machine 18", etc. then the macro just overlooks that and doesn't filter, copy or paste anything?


    Thanks!

    Here's the file, I thought I may as well put the entireity of what I'm trying to achieve in.


    I have a set of data as per the "production data" tab. I want to filter this list by machine (column N) and for every change in column B copy the entire row for that machine to the relevant tab (machine 1, machine 2, machine 3). So the green highlighted rows would be copied to the other tabs.


    The only data I want to copy is from column B, column C (which is just a description), and column E into columns A, B and C of the relevant tab (the columns after that I'll have formulas in so I don't want to overwrite it).


    Does that make sense?


    Thanks.

    Files

    • Data.xlsx

      (94.63 kB, downloaded 62 times, last: )

    Thanks nyatiaju!


    How would I change it if I wanted to filter an entire range (columns A:P)? The code above is filtering column B but the other columns are remaining the same. I would like to filter by column B but filter the entire range.

    Hello, I'm trying to filter a set of data I have. I can't use remove duplicates as I want to retain each unique instance of the field. For example, I may have.....


    Product 1
    Product 1
    Product 1
    Product 1
    Product 2
    Product 2
    Product 2
    Product 1
    Product 1


    I'm trying to filter the range and return the following:


    Product 1
    Product 2
    Product 1


    Thanks in advance!