Macro and automatic update
 

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

Macro and automatic update
 

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

Macro and automatic update
 

Just tried it and it works..........fantastic work!!!!!!

Many thanks

Jade

"Jim Cone" wrote:

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

Macro and automatic update
 

Jade,
You are welcome.
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


----- Original Message -----
From: "Jade"
Newsgroups: microsoft.public.excel.programming
Sent: Tuesday, June 13, 2006 6:33 PM
Subject: Macro and automatic update

Just tried it and it works..........fantastic work!!!!!!
Many thanks
Jade