Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jules
 
Posts: n/a
Default Summarise data on multiple sheets

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
  #2   Report Post  
Jason Morin
 
Posts: n/a
Default

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
.

  #3   Report Post  
Jules
 
Posts: n/a
Default

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
.


  #4   Report Post  
Jason Morin
 
Posts: n/a
Default

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
.


.

  #5   Report Post  
Jules
 
Posts: n/a
Default

Fantastic thank you - now I know how!!!

"Jason Morin" wrote:

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
.


.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
populating sheets based on data from parent sheets seve Excel Discussion (Misc queries) 2 January 15th 05 10:22 PM
Multiple sheets selected twa14 Excel Discussion (Misc queries) 2 December 21st 04 12:15 PM
linking multiple sheets to a summary sheet greg g Excel Discussion (Misc queries) 1 December 16th 04 08:43 AM
How do I plot data in Excel that is captured on separate sheets; . krwegner Excel Discussion (Misc queries) 0 November 30th 04 06:43 PM
sumif to add data in multiple sheets Sues Excel Worksheet Functions 4 November 18th 04 07:54 AM


All times are GMT +1. The time now is 11:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"