Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic update of spreadsheet & automatic update between workboo | Excel Worksheet Functions | |||
Automatic update of footer macro | Excel Worksheet Functions | |||
Macro automatic update | Excel Discussion (Misc queries) | |||
Automatic update | Excel Discussion (Misc queries) | |||
automatic macro update | Excel Worksheet Functions |