Okay, I am stumped on this one. And I know if there is an answer, THIS is where I will find it. First of all, I know almost nothing about VBA. I recorded a macro and I am having to constantly edit it as the number of rows in my pivottable is constantly changing as information is added and removed.
This macro was written to format the PivotTable and copy it, so I can easily paste it into FrontPage. So I do it all in one click. Well, currently when the data changes, I go into the macro and manually edit the number of rows listed in the formula. I would like to avoid having to do this, and I am sure there is a way that Excel can do what I want, I just don't know what it is. below is the VBA code for my macro:
Sub FormatCouponReport()
'
' FormatCouponReport Macro
' Macro recorded 3/6/2005 by Yourwebness
'
'
Columns("C:C").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Columns("D:D").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("A5:E942").Select
Range("E942").Activate
Selection.borders(xlDiagonalDown).LineStyle = xlNone
Selection.borders(xlDiagonalUp).LineStyle = xlNone
With Selection.borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Copy
End Sub
Display More
Thanks in advance for all your help!!!