Summarise data on multiple sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have inherited a invoice workbook which has a sheet for every invoice -
currently about 150 sheets.The invoice setup is identical change the data
differs. I would like to create a summary sheet which lists the invoice no,
date, vendor, nett, vat & gross. All the data is in the same fields on each
sheet but the sheet names differ. The field refs are Invoice no = I21, Date =
A21, Vendor = D11, Nett = I56, Vat = K56 and Gross = M56.
 
Try this macro:

Sub CreateSummary()

'//Constructive criticism from experienced
'//VBA programmers appreciated

Dim ws As Worksheet
Dim i As Integer

Application.ScreenUpdating = False
Sheets(1).Activate
Sheets.Add
With Sheets(1)
.Range("A1").Value = "Invoice No."
.Range("B1").Value = "Date"
.Range("C1").Value = "Vendor"
.Range("D1").Value = "Nett"
.Range("E1").Value = "VAT"
.Range("F1").Value = "Gross"
.Name = "Summary"
i = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Index <> 1 Then
.Rows(i).Cells(1).Value = ws.Range("I21")
.Rows(i).Cells(2).Value = ws.Range("A21")
.Rows(i).Cells(3).Value = ws.Range("D11")
.Rows(i).Cells(4).Value = ws.Range("I56")
.Rows(i).Cells(5).Value = ws.Range("K56")
.Rows(i).Cells(6).Value = ws.Range("M56")
i = i + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub
 
Press ALT+F11. Go to Insert > Module. Paste the code
below into the window. Close VBE (close the window).
You're now back in XL. Go to Tools > Macro > Macros,
click on the this macro, and press RUN.

Jason
 
Fantastic thank you - now I know how!!!

Jason Morin said:
Press ALT+F11. Go to Insert > Module. Paste the code
below into the window. Close VBE (close the window).
You're now back in XL. Go to Tools > Macro > Macros,
click on the this macro, and press RUN.

Jason
Click to expand...
 
Back
Top