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

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

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

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