Thread: 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

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"
<Jade@discussions.microsoft.com>
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

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 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"
> <Jade@discussions.microsoft.com>
> 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
>

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


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

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