Please help with an error VBA code that worked perfectly untill yesterday

    Hello everyone, hello KjBox


    KjBox can you please help me with an error in the vba code that you so very kindly provided me with? It is the vba code that splits rows into the machines, based on the column that contains that number of the machine: it splits the rows from the sheet SPARTISCI containing that machine number in column B ,into the machine sheets; and they are non contiguous columns so you wrote a wonderful code taht worked perfectly untill yesterday.

    I do not know why.

    What I tried to do : two weeks ago, I added in the vba code another column form X sheet (the source sheet) to go into the machine sheets and it still worked like a charm.

    Today, when I had to update the machines after a few days that we weren't working in the company, it gives me an error and highlights this line:

    Code
    .Cells(8, 1).Resize(lRow - 7, 13).Borders.Weight = 2

    Now, why did it start giving error if it worked fine untill yesterday?

    The only thing I added is two new sheets in the same workbook wich however have nothing to do with the machines.

    Funny thing is for some machines it works fine even if it gives me the error: the code puts the rows in some of the machines, gives the error and does not compile other machines.

    Please help me...I have to plann 106 orders ahead tomorrow....I am at lost without your help. I am desperate...this code helped me do my job in the last month in a way I couldn't hoped for otherwise.

    Thank you so so much, I attach the file as it is. Thank you relly in advance

    Change that line of the code to

    Code
    If lRow > 7 Then .Cells(8, 1).Resize(lRow - 7, 13).Borders.Weight = 2

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Quote from KjBox

    Change that line of the code to

    Code
    If lRow > 7 Then .Cells(8, 1).Resize(lRow - 7, 13).Borders.Weight = 2

    Hi KjBox, thank you s so much. I did replace the line and now it does not give me error 1004 anymore, BUT it overwirtes the existing lines in my machne sheet, and that is a problem...

    How can I make the code to work as previously, no matter how many items I added during the day from the sheet SPARTISCI, it added the new items in the machine sheet bellow the last used row. It has to be this way otherwise I loose the preexisting planning on the given machine..

    Can you please help with this too? Thank you from the heart

    I no longer have the file that you originally posted so I can't see exactly what changes you have made, so I am stabbing in the dark a bit here!


    Try changing the entire last With.....End With to

    Code
    With Sheets(CStr(e))
lRow = .Cells(.Rows.Count, 3).End(xlUp).Row + 1
.Cells(lRow, 1).Resize(UBound(y, 1), 14) = y
lRow = .Cells(.Rows.Count, 3).End(xlUp).Row
.Cells(8, 1).Resize(lRow - 7, 13).Borders.Weight = 2
End With

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login