This modified version uses the existing POSummary sheet
and writes the data over the existing data.
If the sheet doesn't exist it creates it...
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub POSummary()
Dim ws As Worksheet
Dim i As Integer

Application.ScreenUpdating = False
On Error Resume Next
Sheets("POSummary").Activate
If Err.Number < 0 Then
On Error GoTo 0
Sheets.Add befo=Sheets(1)
With Sheets(1)
.Range("A1").Value = "Date."
.Range("B1").Value = "Supplier"
.Range("C1").Value = "PO Number"
.Range("D1").Value = "$ Amount"
.Name = "POSummary"
End With
End If

On Error GoTo 0
With Sheets("POSummary")
i = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Index < 1 Then
.Cells(i, 1).Value = ws.Range("H7")
.Cells(i, 2).Value = ws.Range("B8")
.Cells(i, 3).Value = ws.Range("H9")
.Cells(i, 4).Value = ws.Range("P40")
i = i + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub
'------------------


"Jade"

wrote in message
Hi
I have set up a macro that summarises the data that I need on one worksheet.
The only problem is when I enter in more data I have to delete or re-name
that worksheet and run the macro again. I enter data into this worksheet
every day so this would become quite time consuming and annoying.
Here is my current macro

Sub POSummary()
Dim ws As Worksheet
Dim i As Integer

Application.ScreenUpdating = False
Sheets(1).Activate
Sheets.Add
With Sheets(1)
.Range("A1").Value = "Date."
.Range("B1").Value = "Supplier"
.Range("C1").Value = "PO Number"
.Range("D1").Value = "$ Amount"
.Name = "POSummary"
i = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Index < 1 Then
.Rows(i).Cells(1).Value = ws.Range("H7")
.Rows(i).Cells(2).Value = ws.Range("B8")
.Rows(i).Cells(3).Value = ws.Range("H9")
.Rows(i).Cells(4).Value = ws.Range("P40")
i = i + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub

Are there any options for e.g. that I can click something and it will update
my summary worksheet automatically or something similar???????
Thanks