Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
I have several macros that need to be run on each new sheet created.
Maybe I'm thinking too simple, but tried this and not working.... Private Sub Workbook_NewSheet(ByVal sh As Object) Application.Run "apples" Application.Run "oranges" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
Application.Run("apples")
Gord Dibben MS Excel MVP On Wed, 17 Feb 2010 10:56:56 -0800 (PST), "J.W. Aldridge" wrote: I have several macros that need to be run on each new sheet created. Maybe I'm thinking too simple, but tried this and not working.... Private Sub Workbook_NewSheet(ByVal sh As Object) Application.Run "apples" Application.Run "oranges" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
Disregard........works either way for me.
Where are the macros located? Maybe you need to qualify the workbook? Application.Run ("MyMacros.xla!apples") Gord On Wed, 17 Feb 2010 11:17:40 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Application.Run("apples") Gord Dibben MS Excel MVP On Wed, 17 Feb 2010 10:56:56 -0800 (PST), "J.W. Aldridge" wrote: I have several macros that need to be run on each new sheet created. Maybe I'm thinking too simple, but tried this and not working.... Private Sub Workbook_NewSheet(ByVal sh As Object) Application.Run "apples" Application.Run "oranges" End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
macros are located in the same workbook.
still not firing. i actually have 3 macros / procedures i need to fire on the creation of a new worksheet. just can't seem to get them to fire. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
Did you put the code in the ThisWorkbook module?
Did you allow macros to run when you opened the workbook? Did you turn events off and fail to turn them back on. application.enableevents = true 'false is the line you'd search for. And if your macros are in the same workbook module (in General modules, right????), you could use Call instead of application.run: Private Sub Workbook_NewSheet(ByVal sh As Object) Call apples Call oranges End Sub "J.W. Aldridge" wrote: I have several macros that need to be run on each new sheet created. Maybe I'm thinking too simple, but tried this and not working.... Private Sub Workbook_NewSheet(ByVal sh As Object) Application.Run "apples" Application.Run "oranges" End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
thanx...
checked all of the above. also changed to call - nada. any other suggestions to try? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
Two things come to mind, one simple, one strange:
First, are you sure events are firing at all? A lot of times I forget that I stopped some code after a "Application.EnableEvents = False" and then get frustrated when stuff doesn't run. Second, I discovered that using the "Insert/Worksheet" menu item causes the Workbook_NewSheet event to fire, but control-dragging to create a new sheet by copying an existing one doesn't! That's the strange part, and I could not find a workaround. Is that what you are doing? HTH, Eric "J.W. Aldridge" wrote: macros are located in the same workbook. still not firing. i actually have 3 macros / procedures i need to fire on the creation of a new worksheet. just can't seem to get them to fire. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
na. not what i'z doing.
i even tried laying it out this way (to run it on the same sheets that were going to be created) and didn't work. Sub nn() For x = Sheets("IND_BRKDWN").Index + 1 To Worksheets.Count Call PASTE_COUNT Call AAA Call printareamacro Next End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
How are you creating the new sheet?
"J.W. Aldridge" wrote: thanx... checked all of the above. also changed to call - nada. any other suggestions to try? -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
This is how i was creating the worksheets....
Sub Copy_To_Worksheets() 'Note: This macro use the function LastRow and SheetExists Dim My_Range As Range Dim FieldNum As Long Dim CalcMode As Long Dim ViewMode As Long Dim ws2 As Worksheet Dim Lrow As Long Dim cell As Range Dim CCount As Long Dim WSNew As Worksheet Dim ErrNum As Long Dim DestRange As Range Dim Lr As Long 'Set filter range on ActiveSheet: A1 is the top left cell of your filter range 'and the header of the first column, D is the last column in the filter range. 'You can also add the sheet name to the code like this : 'Worksheets("Sheet1").Range("A1:D" & LastRow(Worksheets("Sheet1"))) 'No need that the sheet is active then when you run the macro when you use this. Set My_Range = Range("A1:AB" & LastRow(ActiveSheet)) My_Range.Parent.Select If ActiveWorkbook.ProtectStructure = True Or _ My_Range.Parent.ProtectContents = True Then MsgBox "Sorry, not working when the workbook or worksheet is protected", _ vbOKOnly, "Copy to new worksheet" Exit Sub End If 'This example filters on the first column in the range(change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... FieldNum = 1 'Turn off AutoFilter My_Range.Parent.AutoFilterMode = False 'Change ScreenUpdating, Calculation, EnableEvents, .... With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'Add a worksheet to copy the a unique list and add the CriteriaRange Set ws2 = Worksheets.Add With ws2 'first we copy the Unique data from the filter field to ws2 My_Range.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1"), Unique:=True 'loop through the unique list in ws2 and filter/copy to a new sheet Lrow = .Cells(Rows.Count, "A").End(xlUp).Row For Each cell In .Range("A2:A" & Lrow) My_Range.Parent.Select 'Filter the range My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _ Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?") 'Check if there are no more then 8192 areas(limit of areas) CCount = 0 On Error Resume Next CCount = My_Range.Columns(1).SpecialCells(xlCellTypeVisible ) _ .Areas(1).Cells.Count On Error GoTo 0 If CCount = 0 Then MsgBox "There are more than 8192 areas for the value: " & cell.Value _ & vbNewLine & "It is not possible to copy the visible data." _ & vbNewLine & "Tip: Sort your data before you use this macro.", _ vbOKOnly, "Split in worksheets" Else 'Add a new worksheet or set a reference to a existing sheet If SheetExists(cell.Text) = False Then Set WSNew = Worksheets.Add(After:=Sheets(Sheets.Count)) On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then ErrNum = ErrNum + 1 WSNew.Name = "Error_" & Format(ErrNum, "0000") Err.CLEAR End If On Error GoTo 0 Set DestRange = WSNew.Range("A1") Else Set WSNew = Sheets(cell.Text) Lr = LastRow(WSNew) Set DestRange = WSNew.Range("A" & Lr + 1) End If 'Copy the visible data to the worksheet My_Range.SpecialCells(xlCellTypeVisible).Copy With DestRange .Parent.Select ' Paste:=8 will copy the columnwidth in Excel 2000 and higher ' Remove this line if you use Excel 97 .PasteSpecial Paste:=8 .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False .Select End With End If ' Delete the header row if you copy to a existing worksheet If Lr 1 Then WSNew.Range("A" & Lr + 1).EntireRow.Delete 'Show all data in the range My_Range.AutoFilter Field:=FieldNum Next cell 'Delete the ws2 sheet On Error Resume Next Application.DisplayAlerts = False .Delete Application.DisplayAlerts = True On Error GoTo 0 End With 'Turn off AutoFilter My_Range.Parent.AutoFilterMode = False If ErrNum 0 Then MsgBox "Rename every WorkSheet name that start with ""Error_"" manually" _ & vbNewLine & "There are characters in the name that are not allowed" _ & vbNewLine & "in a sheet name or the worksheet already exist." End If 'Restore ScreenUpdating, Calculation, EnableEvents, .... My_Range.Parent.Select ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
You've turned events off:
.... .EnableEvents = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'Add a worksheet to copy the a unique list and add the CriteriaRange Set ws2 = Worksheets.Add You can turn events off after you do the .add or you could call the macros in this code. "J.W. Aldridge" wrote: This is how i was creating the worksheets.... Sub Copy_To_Worksheets() 'Note: This macro use the function LastRow and SheetExists Dim My_Range As Range Dim FieldNum As Long Dim CalcMode As Long Dim ViewMode As Long Dim ws2 As Worksheet Dim Lrow As Long Dim cell As Range Dim CCount As Long Dim WSNew As Worksheet Dim ErrNum As Long Dim DestRange As Range Dim Lr As Long 'Set filter range on ActiveSheet: A1 is the top left cell of your filter range 'and the header of the first column, D is the last column in the filter range. 'You can also add the sheet name to the code like this : 'Worksheets("Sheet1").Range("A1:D" & LastRow(Worksheets("Sheet1"))) 'No need that the sheet is active then when you run the macro when you use this. Set My_Range = Range("A1:AB" & LastRow(ActiveSheet)) My_Range.Parent.Select If ActiveWorkbook.ProtectStructure = True Or _ My_Range.Parent.ProtectContents = True Then MsgBox "Sorry, not working when the workbook or worksheet is protected", _ vbOKOnly, "Copy to new worksheet" Exit Sub End If 'This example filters on the first column in the range(change the field if needed) 'In this case the range starts in A so Field:=1 is column A, 2 = column B, ...... FieldNum = 1 'Turn off AutoFilter My_Range.Parent.AutoFilterMode = False 'Change ScreenUpdating, Calculation, EnableEvents, .... With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False .EnableEvents = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'Add a worksheet to copy the a unique list and add the CriteriaRange Set ws2 = Worksheets.Add With ws2 'first we copy the Unique data from the filter field to ws2 My_Range.Columns(FieldNum).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1"), Unique:=True 'loop through the unique list in ws2 and filter/copy to a new sheet Lrow = .Cells(Rows.Count, "A").End(xlUp).Row For Each cell In .Range("A2:A" & Lrow) My_Range.Parent.Select 'Filter the range My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _ Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?") 'Check if there are no more then 8192 areas(limit of areas) CCount = 0 On Error Resume Next CCount = My_Range.Columns(1).SpecialCells(xlCellTypeVisible ) _ .Areas(1).Cells.Count On Error GoTo 0 If CCount = 0 Then MsgBox "There are more than 8192 areas for the value: " & cell.Value _ & vbNewLine & "It is not possible to copy the visible data." _ & vbNewLine & "Tip: Sort your data before you use this macro.", _ vbOKOnly, "Split in worksheets" Else 'Add a new worksheet or set a reference to a existing sheet If SheetExists(cell.Text) = False Then Set WSNew = Worksheets.Add(After:=Sheets(Sheets.Count)) On Error Resume Next WSNew.Name = cell.Value If Err.Number 0 Then ErrNum = ErrNum + 1 WSNew.Name = "Error_" & Format(ErrNum, "0000") Err.CLEAR End If On Error GoTo 0 Set DestRange = WSNew.Range("A1") Else Set WSNew = Sheets(cell.Text) Lr = LastRow(WSNew) Set DestRange = WSNew.Range("A" & Lr + 1) End If 'Copy the visible data to the worksheet My_Range.SpecialCells(xlCellTypeVisible).Copy With DestRange .Parent.Select ' Paste:=8 will copy the columnwidth in Excel 2000 and higher ' Remove this line if you use Excel 97 .PasteSpecial Paste:=8 .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False .Select End With End If ' Delete the header row if you copy to a existing worksheet If Lr 1 Then WSNew.Range("A" & Lr + 1).EntireRow.Delete 'Show all data in the range My_Range.AutoFilter Field:=FieldNum Next cell 'Delete the ws2 sheet On Error Resume Next Application.DisplayAlerts = False .Delete Application.DisplayAlerts = True On Error GoTo 0 End With 'Turn off AutoFilter My_Range.Parent.AutoFilterMode = False If ErrNum 0 Then MsgBox "Rename every WorkSheet name that start with ""Error_"" manually" _ & vbNewLine & "There are characters in the name that are not allowed" _ & vbNewLine & "in a sheet name or the worksheet already exist." End If 'Restore ScreenUpdating, Calculation, EnableEvents, .... My_Range.Parent.Select ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .EnableEvents = True .Calculation = CalcMode End With End Sub -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
ok.
i figured that since it turns enable events back on at the end, it would be ok since i run the others after the sheets were created. if not, could i just not turn events off altogther by removing the command? .EnableEvents = False thanx again |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
application.run on new sheets created
Or you could turn it off, do things, turn it on, do more things, turn it off,
...., and end with turning it on. Whichever makes most sense for you and your code. "J.W. Aldridge" wrote: ok. i figured that since it turns enable events back on at the end, it would be ok since i run the others after the sheets were created. if not, could i just not turn events off altogther by removing the command? .EnableEvents = False thanx again -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to reject user operation on the application I created | Excel Programming | |||
Excell Problem when opening file created in another application!! | Excel Discussion (Misc queries) | |||
Defining sheets to be created | Excel Programming | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
Open file in the application that created it | Excel Programming |