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

trouble to arrange invoice information



 the  code   copy  invoice  information   from sheet  INV_PUR  to  sheet client_archive  untidly   . the  right  result  I  put  in  the  third  sheet     .should  brings  numbers sequences  for  column A    and  except  repeating  (DATE,INVOICE NO,CLIENT)  for  TOTAL  row  with brings  word TOTAL  in last row  .I  appreciate  to  fix  that  . 

Sub test()
Dim a, a1
With Sheets("INV_PUR")

a1 = Array(.Range("I10"), .Range("C10"), .Range("E16"))
a = .Range(.Range("B19"), .Range("B19").End(xlDown)).Offset(, 1).Resize(, 7)
With Sheets("client_archive")
lr = .Cells(Rows.Count, 4).End(xlUp).Row + 1
.Range("B" & lr).Resize(UBound(a), 3) = a1
.Range("E" & lr).Resize(UBound(a), UBound(a, 2)) = a
End With: End With
End Sub




Selected Answer


In the attached revised file I'have modified your code as shown below. I created another variable "Items" and collected those (and the Total row) from the invoice so they could be copied into column A of your worksheet client_archive.

I've then modified the sizing of (Range) "a" to collect the remaining invoice item data.

The code looks long since I've added comments and indented your code to help me (and you) understand what is happening:

Sub test()
Dim a, a1, Items

' get data
With Sheets("INV_PUR")
    ' get common data (Date/time, Client and Invoice number)
    a1 = Array(.Range("I10"), .Range("C10"), .Range("E16"))
        'get items numbers (including Total)
    Items = .Range(.Range("B19"), .Range("B19").End(xlDown))
    ' get Invoice item data
    a = .Range(.Range("C19"), .Range("I19").End(xlDown))

    ' archive data
    With Sheets("client_archive")
        ' calculate next free row for column A (1) not  D(4)
        lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        'copy item numbers
        .Range("A" & lr).Resize(UBound(Items), 1) = Items
        'copy common data but without Totals row
        .Range("B" & lr).Resize(UBound(Items) - 1, 3) = a1
        ' copy invoice items
        .Range("E" & lr).Resize(UBound(a), UBound(a, 2)) = a
        ' adjust columns
    End With
End With

End Sub

Note that I added lines to adjust the column widths and also copied the formatting of the first few rows of worksheet client_archive but only to row 21 (cells looked the same but your formatting was not consistent).

Hope this works well for you.



Hi John,
thanks ,but  why  when  run  the  code  will  copy  twice data to  the bottom   if  I  press one time !!
speed (rep: 32) Aug 29, '22 at 12:42 pm
Speed. Press what one time? I don't see a button

I ran the code from VB Explorer and it archives the data only once. Please try that.

If you run the code AGAIN with the same data, it will repeat it in client_archive (since you calculated the last row in that sheet, I assumed you wanted to copy several sets of data below the current contents).
John_Ru (rep: 3992) Aug 29, '22 at 12:52 pm
I no know what happens , but truly this is what happens for me in the beginning . strangely works perfectly now without problem .
thanks very much .
speed (rep: 32) Aug 29, '22 at 7:12 pm
Add to Discussion

Answer the Question

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