Macro and automatic update

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

Guest

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
 
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 before:=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"
<[email protected]>
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
 
Back
Top