VBA procedure when new sheet added event
 

I need sub procedures to run in Excel 2003 when a new sheet is added / copied
from another workbook but nothing is happening. I'm not a VBA expert and not
understanding what I need to do to accomplish this. I have code that reads
info from each new sheet that represents monthly sales data and compiles
stats into a summary sheet1. When a new sheet is added to the workbook I need
that code to run immediately so it updates the summary sheet. If anyone could
give me a clue that would be great.

Thanks

VBA procedure when new sheet added event
 

You should always post your code for comments. How is the new sheet added?

--
Don Guillett
SalesAid Software

"shays" wrote in message
...
I need sub procedures to run in Excel 2003 when a new sheet is added /
copied
from another workbook but nothing is happening. I'm not a VBA expert and
not
understanding what I need to do to accomplish this. I have code that reads
info from each new sheet that represents monthly sales data and compiles
stats into a summary sheet1. When a new sheet is added to the workbook I
need
that code to run immediately so it updates the summary sheet. If anyone
could
give me a clue that would be great.

Thanks

VBA procedure when new sheet added event
 

Noted. I see the sheet added by having both source workbook and destination
workbook open and using Move To book: /Create a copy.
This is the code that I tried to use by example from Help on New Sheet event
but I either don't get it or it doesn't work the way I need it to.

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application

End Sub

'EventClassModule:
Public WithEvents App As Application

Sub Workbook_NewSheet(ByVal sh As Object)
Call FindQuoteTotal
End Sub

Thanks for the assistance Don! Much appreciated.

SHays


"Don Guillett" wrote:

You should always post your code for comments. How is the new sheet added?

--
Don Guillett
SalesAid Software

"shays" wrote in message
...
I need sub procedures to run in Excel 2003 when a new sheet is added /
copied
from another workbook but nothing is happening. I'm not a VBA expert and
not
understanding what I need to do to accomplish this. I have code that reads
info from each new sheet that represents monthly sales data and compiles
stats into a summary sheet1. When a new sheet is added to the workbook I
need
that code to run immediately so it updates the summary sheet. If anyone
could
give me a clue that would be great.

Thanks

VBA procedure when new sheet added event
 

I just recorded this from the source workbook. Of course, it could be
cleaned up. HTH.

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 5/25/2007 by Donald B. Guillett
'

'
Sheets("Sheet19").Select
Sheets("Sheet19").Copy
Befo=Workbooks("destinationworkbook.xls").Sheet s(1)
'destination is NOW the active wb
'call your macro to do something like
Sheets("Sheet1").Select
Range("A5").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
'or sheets("sheet1").range("a5").formula="=sheet2!a5"
'or sheets("sheet1").range("a5").value=sheets("sheet2" ).range("a5")
End Sub

--
Don Guillett
SalesAid Software

"shays" wrote in message
...
Noted. I see the sheet added by having both source workbook and
destination
workbook open and using Move To book: /Create a copy.
This is the code that I tried to use by example from Help on New Sheet
event
but I either don't get it or it doesn't work the way I need it to.

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application

End Sub

'EventClassModule:
Public WithEvents App As Application

Sub Workbook_NewSheet(ByVal sh As Object)
Call FindQuoteTotal
End Sub

Thanks for the assistance Don! Much appreciated.

SHays


"Don Guillett" wrote:

You should always post your code for comments. How is the new sheet
added?

--
Don Guillett
SalesAid Software

"shays" wrote in message
...
I need sub procedures to run in Excel 2003 when a new sheet is added /
copied
from another workbook but nothing is happening. I'm not a VBA expert
and
not
understanding what I need to do to accomplish this. I have code that
reads
info from each new sheet that represents monthly sales data and
compiles
stats into a summary sheet1. When a new sheet is added to the workbook
I
need
that code to run immediately so it updates the summary sheet. If anyone
could
give me a clue that would be great.

Thanks

VBA procedure when new sheet added event
 

Thanks for the example. What event triggers the macro to run is what I am
most interested in working out. Maybe my objective isn't very clear. The
source workbook will contain a sheet that is exported data from a Crystal
Report and will be overwritten each time the report is exported from private
software. The destination sheet is where each months data will be permanently
stored along with a summary sheet for totals. Some of the summary sheet will
be filled in by having each sheet read for the info needed using a procedure.

.....For i = 2 To Sheets.Count
Worksheets(i).Activate
For Each c In Worksheets(i).Range("E1:E500").Cells
If c = "TOTAL" Then
c.Activate
ActiveCell.Offset(ColumnOffset:=3).Select
QuoteTotal = ActiveCell.Value
Select Case Sheets.Count
Case 2
Select Case i
Case 2
Worksheets(1).Cells(i, 3).Value = QuoteTotal
End Select..........

Each time a new sheet is copied to the destination workbook by the user I
will need that procedure to run again to update the summary sheet. The
NewSheet event doesn't appear to work since I'm copying a sheet not
inserting. I have found some success using SheetActivate but I get runtime
errors.

Thanks again!
SHays

"Don Guillett" wrote:

I just recorded this from the source workbook. Of course, it could be
cleaned up. HTH.

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 5/25/2007 by Donald B. Guillett
'

'
Sheets("Sheet19").Select
Sheets("Sheet19").Copy
Befo=Workbooks("destinationworkbook.xls").Sheet s(1)
'destination is NOW the active wb
'call your macro to do something like
Sheets("Sheet1").Select
Range("A5").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
'or sheets("sheet1").range("a5").formula="=sheet2!a5"
'or sheets("sheet1").range("a5").value=sheets("sheet2" ).range("a5")
End Sub

--
Don Guillett
SalesAid Software

"shays" wrote in message
...
Noted. I see the sheet added by having both source workbook and
destination
workbook open and using Move To book: /Create a copy.
This is the code that I tried to use by example from Help on New Sheet
event
but I either don't get it or it doesn't work the way I need it to.

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application

End Sub

'EventClassModule:
Public WithEvents App As Application

Sub Workbook_NewSheet(ByVal sh As Object)
Call FindQuoteTotal
End Sub

Thanks for the assistance Don! Much appreciated.

SHays


"Don Guillett" wrote:

You should always post your code for comments. How is the new sheet
added?

--
Don Guillett
SalesAid Software

"shays" wrote in message
...
I need sub procedures to run in Excel 2003 when a new sheet is added /
copied
from another workbook but nothing is happening. I'm not a VBA expert
and
not
understanding what I need to do to accomplish this. I have code that
reads
info from each new sheet that represents monthly sales data and
compiles
stats into a summary sheet1. When a new sheet is added to the workbook
I
need
that code to run immediately so it updates the summary sheet. If anyone
could
give me a clue that would be great.

Thanks

VBA procedure when new sheet added event
 

I've decided to use a prompt and automate the copy process to include the new
sheet and then run the procedure that updates the summary at the end of that
process. The summary is always updated when the new sheet is added and it
saves the end user from having to perform the copy themselves.

Sub GetNewBidTrackerSheet()
Dim Message, Title
Message = "Import new PDQ Quoted/Booked Worksheet?"
Title = "Import PDQ report"
Response = MsgBox(Message, vbYesNo, Title)
If Response = vbYes Then
Call OpenUp
Else
'Continue Working

End If

End Sub

Sub OpenUp()
Application.ScreenUpdating = False

Workbooks.Open ("C:\QuoteBook.xls")
Workbooks("Bid Tracker.xls").Activate
Workbooks("QuoteBook.xls").Worksheets("Bid Tracker").Copy
After:=Workbooks("Bid Tracker.xls").Sheets(Sheets.Count)
Workbooks("QuoteBook.xls").Close

Call FindQuoteTotal

Application.ScreenUpdating = True

End Sub



"shays" wrote:

Thanks for the example. What event triggers the macro to run is what I am
most interested in working out. Maybe my objective isn't very clear. The
source workbook will contain a sheet that is exported data from a Crystal
Report and will be overwritten each time the report is exported from private
software. The destination sheet is where each months data will be permanently
stored along with a summary sheet for totals. Some of the summary sheet will
be filled in by having each sheet read for the info needed using a procedure.

....For i = 2 To Sheets.Count
Worksheets(i).Activate
For Each c In Worksheets(i).Range("E1:E500").Cells
If c = "TOTAL" Then
c.Activate
ActiveCell.Offset(ColumnOffset:=3).Select
QuoteTotal = ActiveCell.Value
Select Case Sheets.Count
Case 2
Select Case i
Case 2
Worksheets(1).Cells(i, 3).Value = QuoteTotal
End Select..........

Each time a new sheet is copied to the destination workbook by the user I
will need that procedure to run again to update the summary sheet. The
NewSheet event doesn't appear to work since I'm copying a sheet not
inserting. I have found some success using SheetActivate but I get runtime
errors.

Thanks again!
SHays

"Don Guillett" wrote:

I just recorded this from the source workbook. Of course, it could be
cleaned up. HTH.

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 5/25/2007 by Donald B. Guillett
'

'
Sheets("Sheet19").Select
Sheets("Sheet19").Copy
Befo=Workbooks("destinationworkbook.xls").Sheet s(1)
'destination is NOW the active wb
'call your macro to do something like
Sheets("Sheet1").Select
Range("A5").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
'or sheets("sheet1").range("a5").formula="=sheet2!a5"
'or sheets("sheet1").range("a5").value=sheets("sheet2" ).range("a5")
End Sub

--
Don Guillett
SalesAid Software

"shays" wrote in message
...
Noted. I see the sheet added by having both source workbook and
destination
workbook open and using Move To book: /Create a copy.
This is the code that I tried to use by example from Help on New Sheet
event
but I either don't get it or it doesn't work the way I need it to.

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application

End Sub

'EventClassModule:
Public WithEvents App As Application

Sub Workbook_NewSheet(ByVal sh As Object)
Call FindQuoteTotal
End Sub

Thanks for the assistance Don! Much appreciated.

SHays


"Don Guillett" wrote:

You should always post your code for comments. How is the new sheet
added?

--
Don Guillett
SalesAid Software

"shays" wrote in message
...
I need sub procedures to run in Excel 2003 when a new sheet is added /
copied
from another workbook but nothing is happening. I'm not a VBA expert
and
not
understanding what I need to do to accomplish this. I have code that
reads
info from each new sheet that represents monthly sales data and
compiles
stats into a summary sheet1. When a new sheet is added to the workbook
I
need
that code to run immediately so it updates the summary sheet. If anyone
could
give me a clue that would be great.

Thanks