Posts by crshuby

    I modified it a little to try and get to add the last two columns (18 and 19). I added a parenthesis at the end of row 2 and that stopped the error. Now it won't copy anything over. That is OK though. I really don't need those last two columns. It was a 'nice to have' not a requirement.


    Thanks,

    Carl


    Code
    1. If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 And c.Value <> vbNullString Then
    2. MyRw = sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1)
    3. sh2.Range("A" & MyRw).Resize(1, 6) = c.Resize(1, 6).Value
    4. sh2.Range(cells(MyRw, 5), cells(MyRw, 6)) = sh1.range(sht1.cells(c.row, 18), sh1.cells(c.row, 19))
    5. End if

    Jolivanes I did not get a chance to try it yet because the other one worked.


    Justin - for some reason the line

    Code
    1. MyRw = sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1

    Gives a compile error and will not run

    One more question. If I want to transfer to other values with the PO Row that are in columns 18 and 19 how would I change this line to do that?


    Code
    1. If WorksheetFunction.CountIf(sh2.Range("A:A"), c.Value) = 0 And c.Value <> vbNullString Then sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row)(2).Resize(1, 4) = c.Resize(1, 4).Value

    The 2nd 5 worked but it wiped out entries that i put in by hand. The first 5 lines (If statement) worked better but did not preserve the order. Is it possible just to append any new lines to the last open line of Sheet 2?

    OK back to double clicking again. It worked at first but now it is not. I put a Msgbox at before the loop and when I click once the message box does not appear. So I know on that first click the macro is not even running.

    I managed to fix part of it by adding

    Code
    1.  Application.CutCopyMode = False

    just before End Sub. So now it works on the first click.

    Now I need to figure why it appends a line with no PO number when you press the button and there are no new POs to add.

    Yes I read the forum rules. I thought I explained it. It is supposed to review column B in Sheet 1 and see if column A in Sheet 2 contains any of the numbers in Sheet 1 column B. If it does not it is supposed to append the row (columns B,C,D&E only) to the Sheet 2 tab in columns A-D. When I click on the button that is supposed to do this, usually nothing copies. When I click on it a 2nd time, it works correctly like it is supposed to work. If I click on and no new PO numbers need to be added, it appears to add the last row without a PO number in it.

    I found out If I run it with a blank sheet 2, it works correctly, But then if I run it again, it should not add anything because there are no new ones to add. It is supposed to add only lines that contain P.O. numbers. The second time through it adds one more line, the last row that has "" for P.O. number.

    You can do this with conditional formatting in the cells in column D.

    Click on the date cell in column D

    Select Conditional Formatting from the toolbar ribbon

    You are going to make 4 rules on a single cell setting all your conditions

    Each time you will select "Use a formula to determine which cells to format"

    Each formula will set the conditions for that format

    Enter (TODAY()-$D$1)<11 in the formula box

    select the Format button and format the Fill Green

    Select OK

    make another rule for that cell the same way except type this in the formula bar

    AND((TODAY()-$D$1)>10,(TODAY()-$D$1)<15)

    select the Format button and Fill Yellow

    next rule

    (TODAY()-$D$1)>14

    select Format button and Fill Red

    last rule

    $C$1="Complete"

    select Format button and Fill Green


    Hit OK and it should work

    I did this all in cell D1, you will have to do it in whatever cell you start with then you can copy the formatting down the column.



    You should be able to it all with conditional formatting.

    Click on it and start a new rule. while you are in a cell in column I

    Select "Use a formula to determine which cell to format"

    Then enter your formula

    =$G$6 > 0

    then format the font to be black (that is the default so you may not even need this rule)

    then enter another conditional format on the same cell

    =$G$6 = ""

    then format the font to white


    Do this to all cells in column I that you want this format

    This is my code to check a column is sheet 2 and if the information from sheet 1 is not there, then add the information in columns B through E to the next empty row.

    The problem is that it is adding a row after the last valid row I want to add.