Invoice save data on another sheet

[michellin]

Hi there,

Yes i'm a noob, i try to make myself a simple invoice and keep data for my taxe, for my tiny side line.

I got on the sheet'' Facture Papillon'' 15 cell i want to be copy when i run the macro, on a range '' A2 to O2'' on sheet ''VENDU''. But each time on the next empty range. (A2:O2), next time (A3:O3)

Like this:

Sheet''Facture Papillon'' cell(F4) TO Sheet''VENDU'' cell(A2)
Sheet''Facture Papillon'' cell(F5) TO Sheet''VENDU'' cell(B2)
Sheet''Facture Papillon'' cell(F6) TO Sheet''VENDU'' cell(C2)
Sheet''Facture Papillon'' cell(D11) TO Sheet''VENDU'' cell(D2)
Sheet''Facture Papillon'' cell(D12) TO Sheet''VENDU'' cell(E2)
Sheet''Facture Papillon'' cell(D13) TO Sheet''VENDU'' cell(F2)
Sheet''Facture Papillon'' cell(D14) TO Sheet''VENDU'' cell(G2)
Sheet''Facture Papillon'' cell(D15) TO Sheet''VENDU'' cell(H2)
Sheet''Facture Papillon'' cell(D16) TO Sheet''VENDU'' cell(I2)
Sheet''Facture Papillon'' cell(FD17) TO Sheet''VENDU'' cell(J2)
Sheet''Facture Papillon'' cell(D20) TO Sheet''VENDU'' cell(K2)
Sheet''Facture Papillon'' cell(D21) TO Sheet''VENDU'' cell(L2)
Sheet''Facture Papillon'' cell(D22) TO Sheet''VENDU'' cell(M2)
Sheet''Facture Papillon'' cell(D23) TO Sheet''VENDU'' cell(N2)
Sheet''Facture Papillon'' cell(D24) TO Sheet''VENDU'' cell(O2)

I search here a lot, i found thing looking a bit like that but never be able to modify it to make it work for me.

Il give you a link to my invoice in gdrive

Factures Les papillons de Francois.xlsx

drive.google.com

Don't worry about my sheet being in french, i will translate all.
And no i'm not posting on the french side, because everytimes i've done that nobody was anwsering after some days.
I know the english side to be more visit then the french side.

Thanks in advance to you mister or miss VBA pro

Michellin

 

[Logit]

Here is a link to a simple database that has ALL the parts you need. You simply have to expand on what is already there
to cover everything in your project :

Simple Database.xlsm

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
    
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

 

[michellin]

Thanks Mr Logit

I will make research on those function.

I'm too noob to see where the macro found is value and paste it.

I will read on it and learn more.

Thanks you for your answer,

 

[michellin]

Here is a link to a simple database that has ALL the parts you need. You simply have to expand on what is already there
to cover everything in your project :

Simple Database.xlsm

Option Explicit
Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
   
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
   
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
   
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
   
End Sub
Sub CommandButton1_Click()
    Selection.EntireRow.Delete
End Sub

Hy Mr Logit

I think i'm really too stupid to understand what i'm suppose to expand. After to try to play with that simple database. That does nothing of what i need.

You give me a userform to enter value on next line on one sheet.

I just need to to copy value from one sheet to another one, on the next range.

i saw that line newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1

use to put data on the next row, but they took value from a userform.

Sorry for my nooblithing but i can't not see how to put it to my sauce and expand it.

Maybe if you could make me a little example with one line, i could figure it for the rest

like for that one : Sheet''Facture Papillon'' cell(F4) TO Sheet''VENDU'' cell(A2)
and i would do it for the next 14 other.

michellin

 

[Logit]

My apologies ... I gave you the wrong project.

The following copies the pertinent data from the Invoice (Sheet2) to the Summary (Sheet3).

Option Explicit

Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = Sheet3
    Dim newRow As Long
    
    'Increment to next empty row
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    'Copy/Paste Invoice data to Summary
    
    ws.Cells(newRow, 1).Value = Sheet2.Range("E10").Value
    ws.Cells(newRow, 2).Value = Sheet2.Range("E9").Value
    ws.Cells(newRow, 3).Value = Sheet2.Range("B10").Value
    ws.Cells(newRow, 4).Value = Sheet2.Range("B14").Value
    ws.Cells(newRow, 5).Value = Sheet2.Range("E22").Value
    
    'Clear fields on Sheet2
    Sheet2.Range("B10, B12,B14,B17:B20,E21").Value = ""
    
    'Increment Invoice Number
    Sheet2.Range("E9").Value = Sheet2.Range("E9") + 1
    
End Sub

Download sample project : Invoice Sample.xlsm

 

[michellin]

My apologies ... I gave you the wrong project.

The following copies the pertinent data from the Invoice (Sheet2) to the Summary (Sheet3).

Option Explicit

Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = Sheet3
    Dim newRow As Long
   
    'Increment to next empty row
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
   
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    'Copy/Paste Invoice data to Summary
   
    ws.Cells(newRow, 1).Value = Sheet2.Range("E10").Value
    ws.Cells(newRow, 2).Value = Sheet2.Range("E9").Value
    ws.Cells(newRow, 3).Value = Sheet2.Range("B10").Value
    ws.Cells(newRow, 4).Value = Sheet2.Range("B14").Value
    ws.Cells(newRow, 5).Value = Sheet2.Range("E22").Value
   
    'Clear fields on Sheet2
    Sheet2.Range("B10, B12,B14,B17:B20,E21").Value = ""
   
    'Increment Invoice Number
    Sheet2.Range("E9").Value = Sheet2.Range("E9") + 1
   
End Sub

Download sample project : Invoice Sample.xlsm

Hy Mr Logit.

Yes i'm not crazy

I can understand more this one for now. Take all the data on sheet to and vopy it to sheet 3, then erase the data and ad one to the invoice.

Like you said earlier, that look to full up all my answer. I will play with it tonight, trying to put it to my sauce. And i let you know. Thanks a lot

Michellin

 

[Logit]

Best wishes adopting the code to match your project. I am certain you will accomplish your goal.

 

[michellin]

Best wishes adopting the code to match your project. I am certain you will accomplish your goal.

Hy Mr Logit

Here we are, i got something running perfectly.

Thanks a lot to your help.

Here my finish product and translate

Sub SAVE_DATA()
Dim ws As Worksheet
Set ws = Feuil3
Dim newRow As Long

newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1

'The next two lines can be expanded as many times as needed for all the entry fields in your project

ws.Cells(newRow, 1).Value = Feuil1.Range("F4").Value
ws.Cells(newRow, 2).Value = Feuil1.Range("F5").Value
ws.Cells(newRow, 3).Value = Feuil1.Range("F6").Value
ws.Cells(newRow, 4).Value = Feuil1.Range("D11").Value
ws.Cells(newRow, 5).Value = Feuil1.Range("D12").Value
ws.Cells(newRow, 6).Value = Feuil1.Range("D13").Value
ws.Cells(newRow, 7).Value = Feuil1.Range("D14").Value
ws.Cells(newRow, 8).Value = Feuil1.Range("D15").Value
ws.Cells(newRow, 9).Value = Feuil1.Range("D16").Value
ws.Cells(newRow, 10).Value = Feuil1.Range("D17").Value
ws.Cells(newRow, 11).Value = Feuil1.Range("D20").Value
ws.Cells(newRow, 12).Value = Feuil1.Range("D21").Value
ws.Cells(newRow, 13).Value = Feuil1.Range("D22").Value
ws.Cells(newRow, 14).Value = Feuil1.Range("D23").Value
ws.Cells(newRow, 15).Value = Feuil1.Range("F24").Value

Feuil1.Range("F6,D11,B14,B20:C23").Value = ""
Feuil1.Range("F5").Value = Feuil1.Range("F5") + 1
End Sub

Thanks a lot again

Michellin

 

[Logit]

Glad you have an answer.

Cheers !