Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
populating sheets based on data from parent sheets | Excel Discussion (Misc queries) | |||
Multiple sheets selected | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) | |||
How do I plot data in Excel that is captured on separate sheets; . | Excel Discussion (Misc queries) | |||
sumif to add data in multiple sheets | Excel Worksheet Functions |