Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Personal.xls macro that writes to ThisWorkBook
I've writen a macro that only runs when my user needs to run the marco. Here
is what the marco does: 1) User opens a SAP report which needs to be formated. 2) User runs my macro to format the report 3) Macro also creats a PivotTable on worksheet "Discounts". 4) User might need to update one of the fields in Data worksheet and if the user wants to have the PivotTable updated, they have to click on the PivotTable and then Refresh the data. Here is what I would like to do: I would like it so that when the macro runs, it would put some code in the "ThisWorkbook" that will do the following : Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim DisSheet, CurSheet DisSheet = "Discounts" CurSheet = ActiveCell.Worksheet.Name If CurSheet = DisSheet Then GoTo 10 If CurSheet < DisSheet Then GoTo 20 10: Sheets("Discounts").Select Range("A3").Select ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh 20: End Sub How do I do this by adding it to my Macro in my Personal.xls file? Derek |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Personal.xls macro that writes to ThisWorkBook
I've writen a macro that only runs when my user needs to run the marco. Here
is what the marco does: 1) User opens a SAP report which needs to be formated. 2) User runs my macro to format the report 3) Macro also creats a PivotTable on worksheet "Discounts". 4) User might need to update one of the fields in Data worksheet and if the user wants to have the PivotTable updated, they have to click on the PivotTable and then Refresh the data. Here is what I would like to do: I would like it so that when the macro runs, it would put some code in the "ThisWorkbook" that will do the following : Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim DisSheet, CurSheet DisSheet = "Discounts" CurSheet = ActiveCell.Worksheet.Name If CurSheet = DisSheet Then GoTo 10 If CurSheet < DisSheet Then GoTo 20 10: Sheets("Discounts").Select Range("A3").Select ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh 20: End Sub How do I do this by adding it to my Macro in my Personal.xls file? I should also add that the SAP report file is going to be new each time so it not like the same file will be used each time. Derek |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Personal.xls macro that writes to ThisWorkBook
Chip Pearson shares some VBA techniques for writing code that writes code.
http://www.cpearson.com/excel/vbe.aspx Derek Bliss wrote: I've writen a macro that only runs when my user needs to run the marco. Here is what the marco does: 1) User opens a SAP report which needs to be formated. 2) User runs my macro to format the report 3) Macro also creats a PivotTable on worksheet "Discounts". 4) User might need to update one of the fields in Data worksheet and if the user wants to have the PivotTable updated, they have to click on the PivotTable and then Refresh the data. Here is what I would like to do: I would like it so that when the macro runs, it would put some code in the "ThisWorkbook" that will do the following : Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim DisSheet, CurSheet DisSheet = "Discounts" CurSheet = ActiveCell.Worksheet.Name If CurSheet = DisSheet Then GoTo 10 If CurSheet < DisSheet Then GoTo 20 10: Sheets("Discounts").Select Range("A3").Select ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh 20: End Sub How do I do this by adding it to my Macro in my Personal.xls file? Derek -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Personal.xls macro that writes to ThisWorkBook
If anyone is wondering, this is what I added to my code:
' This adds the refresh to the "Discounts" tab. Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Const DQUOTE = """" ' one " character Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("ThisWorkBook") Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CountOfLines + 1 .InsertLines LineNum, "Private Sub Workbook_SheetActivate(ByVal Sh As Object)" LineNum = LineNum + 1 .InsertLines LineNum, "Dim DisSheet, CurSheet" LineNum = LineNum + 1 .InsertLines LineNum, "DisSheet = " & DQUOTE & "Discounts" & DQUOTE LineNum = LineNum + 1 .InsertLines LineNum, "CurSheet = ActiveCell.Worksheet.Name" LineNum = LineNum + 1 .InsertLines LineNum, "If CurSheet = DisSheet Then GoTo 10" LineNum = LineNum + 1 .InsertLines LineNum, "If CurSheet < DisSheet Then GoTo 20" LineNum = LineNum + 1 .InsertLines LineNum, "10: Sheets(" & DQUOTE & "Discounts" & DQUOTE & ").Select" LineNum = LineNum + 1 .InsertLines LineNum, "Range(" & DQUOTE & "A3" & DQUOTE & ").Select" LineNum = LineNum + 1 .InsertLines LineNum, "ActiveSheet.PivotTables(" & DQUOTE & "PivotTable1" & DQUOTE & ").PivotCache.Refresh" LineNum = LineNum + 1 .InsertLines LineNum, "20:" LineNum = LineNum + 1 .InsertLines LineNum, "End Sub" End With Thanks Dave "Dave Peterson" wrote: Chip Pearson shares some VBA techniques for writing code that writes code. http://www.cpearson.com/excel/vbe.aspx Derek Bliss wrote: I've writen a macro that only runs when my user needs to run the marco. Here is what the marco does: 1) User opens a SAP report which needs to be formated. 2) User runs my macro to format the report 3) Macro also creats a PivotTable on worksheet "Discounts". 4) User might need to update one of the fields in Data worksheet and if the user wants to have the PivotTable updated, they have to click on the PivotTable and then Refresh the data. Here is what I would like to do: I would like it so that when the macro runs, it would put some code in the "ThisWorkbook" that will do the following : Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim DisSheet, CurSheet DisSheet = "Discounts" CurSheet = ActiveCell.Worksheet.Name If CurSheet = DisSheet Then GoTo 10 If CurSheet < DisSheet Then GoTo 20 10: Sheets("Discounts").Select Range("A3").Select ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh 20: End Sub How do I do this by adding it to my Macro in my Personal.xls file? Derek -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ThisWorkbook of personal.xls | Excel Discussion (Misc queries) | |||
Creating a macro that writes to all worksheets | Excel Programming | |||
Macro that writes Macros | Excel Programming | |||
Personal macro workbook and personal.xls | Excel Discussion (Misc queries) |