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
-----Original Message-----
Great except I'm clueless on VBA - so how do I add this
macro to the workbook?
Tx
"Jason Morin" wrote:
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
---
HTH
Jason
Atlanta, GA
-----Original Message-----
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.
--
Tx
Jules
.
.
|