Code to Copy & Paste/ Clear Range

[Bart60]

Hi,

In the Invoice Data2 sheet there are cells highlighted yellow that I would like to have posted to the Database Table A:K starting in row 17 like in my example please. Can one button post this data and clear for new or should there be 2 buttons?

Thank you very much for your help

[queuesef]

It would be best to have 2 buttons; one that Copies the data to the Database; and the other to Clear for new items.

[Bart60]

Okay great thanks queuesef. Are you able to derive the codes?

[maras_mak]

In my opinion, both ranges are the same, so all you need is one button.
New data will be written when you use Button 'Add'.
Assign my code to the 'Add' button.

Please Login or Register  to view this content.

[Bart60]

Thanks maras_mak I implemented your code.

The REF column is not matching the numbers for data and would it be possible to not have the blank rows? The 6, 7, 8, 9 don't have data but are copied.

How to clear the cells for next time?

[maras_mak]

Bart60, cells are automatically cleaned before entering new values.
Correct my code to this.

Please Login or Register  to view this content.

[Bart60]

That works better thanks. I don't understand how the yellow cells get cleared when I want to enter another invoice.

Can you please explain?

[Bart60]

Maras,

I am sorry for not being clear. I intended for the database to grow with new invoices as they are added and not replaced.

Can this code post the data to the next available empty row instead of replacing?

[Marc L]

Can one button post this data and clear for new or should there be 2 buttons?

One button is enough so according to your attachment a starter demonstration
to paste to the Invoice Data2 worksheet module :

Sub Demo1() 
   Dim R& 
       R = [H3:K13].Find("*", , xlValues, , xlByRows, xlPrevious).Row - 3 
    If R Then 
        With [A16].ListObject.Range.Columns(1).Find("*", , , , , xlPrevious)(2) 
            .Resize(R, 6).Value = [A4:F4].Value2 
            .Offset(, 6).Resize(R, 5).Value2 = [G4:K4].Resize(R).Value2 
        End With 
             [A4:F4,H4:K13].ClearContents 
    End If 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[maras_mak]

Bart60, now everything is clear to me.
Look at the attachment.

[Bart60]

@Marc L

I tried your code but I have requests please. I did some adjustments of the sheet and there are also formulas that I would like to stay so I changed the Clear Contents part of the code.

I have an issue now if I only enter one line item (service or material) it doesn't post the data or clear the area. Also if I have more that one item let's say 4 only 3 line items are posted.

If you have the time can you check please?

Thank you

[Marc L]

Your bad : delete Module1 then this time well read & apply the dark red direction just before the code !
And amend the ClearContents codeline for the ranges to clear …

[maras_mak]

Bart60, if you want use my code adapted to your new data layout.

Please Login or Register  to view this content.

[Bart60]

Thank you Maras for the edited code to fit my new layout. I opted to use the code from Marc L it seems less complicated. I hope you can understand my choice.

Please Login or Register  to view this content.

[maras_mak]

You're welcome. The choice is yours.

[Marc L]

I opted to use the code from Marc L

Bad mod in the first Find codeline as the search range must contain the header so it's I1:I11 then - 1 is the good value at end …

[Bart60]

Revisit results sometimes the Cust ID is posting as a date not sure why. It happens with either code.

[Marc L]

Format the source cell as text and if the issue remains attach a workbook accordingly …

[Bart60]

Changing the "2" to a "1" seems to work thanks Marc L