making a query for my invoice system in excel

[colatje]

Hi All,

i made an invoice system in excel, its working well, but now i would like to make a query of all the articles i sold and put it in one excel worksheet for every year.
Every excel invoice has an number like this 2010xxx or 2011xxx. The articles of the invoices are in the range c18:c42. Some of the cells are empty so i would only want the cells where an article is in.
The end result i really want is an overview of the articles is sold for every invoice.

Example:
Invoice Number Article Price
2011001 1 mouse 15,4
2011001 2 cartridge 12,3
2011002 1 desktop.. 523
2011003 3 utp-5m 6,23
2011003 1 work 30
2011003 5 router 35

Is this possible to write in VBA for Excel?

thx!

Brecht

[royUK]

Maybe a PivotTable

[JapanDave]

Hey , This is pretty crude, but it does the job.

Sub Dave()

Dim LastRow As Long
Dim ws As Worksheet
Dim qry As Worksheet
Application.ScreenUpdating = False

   Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Query"
  
Set ws = Worksheets("Sheet1")
Set qry = Worksheets("Query")
  ws.Range("C18:E42").Copy Destination:=qry.Cells(1, 5)
  LastRow = qry.Cells(qry.Rows.Count, 1).End(xlUp).Row
       With qry
          .Range("H1").Resize(25).FormulaR1C1 = "=LEFT(RC[-3],4)"
          .Rows("1:1").AutoFilter
          .Range("$E$1:$H$25").AutoFilter Field:=4, Criteria1:="2011"
          .UsedRange.Copy Destination:=Range("A1")
          .Columns("D:H").Delete Shift:=xlToLeft
       End With
Application.ScreenUpdating = True
End Sub