Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copy data of invoice to data sheet

0

hello

I  try   copy  data  invoice    to  another  sheet  like  summary     this  is  my  code  not  work  , and  I  put  expected result  in sheet "data" 

note :  it  supposes  copying to  the  bottom under old  data  every time  i  copy a new  data  should  copy  under  them not  overwrite 

Sub saveButton()
 Set ws = Sheets("Invoice 2")
 Set Rs = Sheets("DATA")
 LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
  ws.Range("A" & LastRow) = Rs.Range("F2")
  ws.Range("B" & LastRow) = Rs.Range("F3")
  ws.Range("C" & LastRow) = Rs.Range("F4")
  ws.Range("D" & LastRow) = Rs.Range("F5")
  ws.Range("E" & LastRow) = Rs.Range("A" & LastRow)
End Sub

thanks in advance 

Answer
Discuss

Discussion

You have hit upon a known problem when recording invoice data:- To comply with database rules you want only one line per invoice. But what to do if the invoioce has more than one line?
The answer is to have two data tables. One for the invoice itself: Number, Customer, Date, Total, Discount, Tax, Terrms etc. The other for invoice items: Inv Number, Item, Qty, Unit price.
Variatus (rep: 4889) Dec 4, '20 at 7:02 pm
hi, variatus     even  i  follow  your  way  the  code  doesn't  work  at  all  i  no  kinow   what's  the problem   and  no  gives  me any error 
leopard (rep: 88) Dec 4, '20 at 11:53 pm
Add to Discussion

Answers

0

Leopard

As written, your macro copies cells FROM the Data sheet to Invoice (but your question implies that you want to copy from Invoice TO Data instead).

If so, you need to change the assignments so Rs is set to Invoice not Data (see changes in bold below):

Sub saveButton()
 Set Rs = Sheets("Invoice 2")
 Set ws = Sheets("DATA")
 LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
  ws.Range("A" & LastRow) = Rs.Range("F2")
  ws.Range("B" & LastRow) = Rs.Range("F3")
  ws.Range("C" & LastRow) = Rs.Range("F4")
  ws.Range("D" & LastRow) = Rs.Range("F5")
  ws.Range("E" & LastRow) = Rs.Range("A" & LastRow)
End Sub

Then if the last used row in Data is 49 say, the macro will copy  F1, F3, F3 and F4 from Invoice 2 to Data sheet A50, B50, C50 and D50.

Data E50 however will get cell A50 from Invoice 2 (from =Rs.Range("A" & LastRow)  in bold italics above).

Hope this helps.

Discuss

Discussion

hi, john    i did  it  nothing  changes
leopard (rep: 88) Dec 4, '20 at 11:55 pm
Revision 1: Read before my original reply below please!
Sorry but I didn't see the file in your question. The revised code does work but you will only see it if you first clear rows in the Data sheet. I leave my original rely beow (in casse it's useful to you in other situtations).. Good to see that @ Variatus has given you a working solution anyway.

Original reply: In which case I suspect that either your sheets are protected (so can't be written to) or your Data sheet  has something in column A beyond first visible rows (so the last row isn't seen.).
To test the latter, either 1) Put odd text in cell F2 of invoice 2, run the revised code above and use Find to locate that odd text in the Data sheet
or
2) select a cell in column A of the Data sheet then type in VB Explorer's Intermediate Window 
Debug.Print Range("A" & Rows.Count).End(xlUp).Row
then hit Return. It will print the last used row from Data (below the code) - look there.
John_Ru (rep: 6152) Dec 5, '20 at 2:01 am
hi john   ,  actaully  my   sheet   is  not  protected  and  i  select  your  choice 2   and   gives  me   the  lastrow =46    this   is   the  lastrow  contain data   as  in  my  sheet  
leopard (rep: 88) Dec 5, '20 at 5:41 am
Leopard, you get 46 is the Invoice 2 sheet is selected (but I asked you to pick Data).

Yoou should get these results (when you first select the sheet in the comment):
'With "Data" sheet selected:
Debug.Print Range("A" & Rows.Count).End(xlUp).Row
 2 
 
'With "Invoice 2" sheet selected:
Debug.Print Range("A" & Rows.Count).End(xlUp).Row 
 46 
 


The problem was not what I guessed but (only if the answer from Variatus does not work for you):

1) With some test data in the Invoice 2 sheet F2:F5, add the line in bold below to the revised code I sent in my answer:
...
  ws.Range("E" & LastRow) = Rs.Range("A" & LastRow)
  ws.Range("A" & LastRow& ":" & "E" & LastRow).Font.Italic = True

2) Clear rows 2 to 10 in Data

3) Run the revised macro just above. 

You will find that text from Invoice 2 is in Data in columns A to D in italics (to show it worked). If that's in A2:D2, then E2 will be formatted as italics but empty (since A47 of Invoice is empty, as I said in an earlier point.

Hope this works (then you can comment out the new line or set it to false)
John_Ru (rep: 6152) Dec 5, '20 at 7:33 am
hi, john  finally   i found  the  copying data in dheet data from row 47  and  i  change from this
 LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

  
to  this 
 LastRow = ws.Range("e" & Rows.Count).End(xlUp).Row + 1


then it copy from row 2 but not add to bottom ,it replaces what copy earlier


about  code variatus  is  perfect   but  there is  problem  some  data in specific column is not existed it  keeps  empty  so  i wait    for  him  to  fixing
leopard (rep: 88) Dec 5, '20 at 10:11 am
Sorry Leopard but I'm not sure what you're saying (or if my suggestions helped).
What is clear is that you will use the answer from Variatus (but need him to fix something because you can't).
Accordingly I will step back from this discussion and leave it to you and Variatus. 
John_Ru (rep: 6152) Dec 5, '20 at 10:42 am
Add to Discussion
0

OK. Please try this code. Paste it to a standard code module.

Sub SaveButton_Click()
    ' 131

    Dim Inv         As Variant              ' Invoice data
    Dim Tmp         As Variant              ' Invoice items data
    Dim Items       As Variant              ' Tmp w/o "Taxed" column
    Dim R           As Long                 ' row (diverse uses)
    Dim C           As Long                 ' loop counter: Tmp, 2

    With Worksheets("Invoice 2")
        Inv = .Range("F3:F6").Value
        ' each row must have a value in column A
        R = .Cells(17, "A").End(xlDown).Row
        Tmp = .Range(.Cells(17, "A"), .Cells(R, "F")).Value
    End With

    ' remove data for column "Taxed" from the invoice items
    ReDim Items(1 To UBound(Tmp), 1 To UBound(Tmp, 2) - 1)
    For R = 1 To UBound(Tmp)
        For C = 1 To UBound(Items, 2)
            ' removes the next to last item in a row
            Items(R, C) = Tmp(R, C + IIf(C = UBound(Items, 2), 1, 0))
        Next C
    Next R

    Application.ScreenUpdating = False
    With Worksheets("DATA")
        R = .Cells(.Rows.Count, "E").End(xlUp).Row + 1
        .Range("A2:I2").Copy                ' format new cells like first row
        .Cells(R, "A").Resize(UBound(Items), UBound(Items, 2) + UBound(Inv)) _
                      .PasteSpecial xlPasteFormats
        .Cells(R, "A").Resize(1, UBound(Inv)).Value = Application.Transpose(Inv)
        .Cells(R, "E").Resize(UBound(Items), UBound(Items, 2)).Value = Items
    End With
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

Edit 04 Dec 2020   ===============================

If you wish to delete the data in the "Invoice" sheet after copying please replace the first code block in the above procedure with the one below.

    With Worksheets("Invoice 2")
        With .Range("F3:F6")
            Inv = .Value
            .ClearContents
        End With
        ' each row must have a value in column A
        R = .Cells(17, "A").End(xlDown).Row
        With .Range(.Cells(17, "A"), .Cells(R, "F"))
            Tmp = .Value
            .ClearContents
        End With
    End With
Discuss

Discussion

perfect  just  if  is  possible   i  would  clear  data  in sheet("invoice") after copy  to  sheet ("data")  to  statrt   a new   process and  do not  repeat  old data again  
leopard (rep: 88) Dec 5, '20 at 2:57 am
i 'v found  out  column f  is  empty    it  supposes column i   =  total   in f38   as  in  my  file 
thanks  again
leopard (rep: 88) Dec 5, '20 at 5:35 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login