A VBA code for automatic Refresh in PV and formatting.
When I work in PT, it is irritating and time consuming to refresh data after changing the data source. I have also need to adjust the width columns and sometimes the number formatting and font size.
I am aware there is a time even driven function in VBA(Application.Ontime), but I do not know how to write this code.
What I would like to see is: the number formatting and column width should automatically adjusted either if I click the refresh button in PT, or every five minutes.
Is it possible to do in excel, if so, could some one kindly can come up with a code please ?
Thanks in advance.
Related resources
Replies (4)
Please login or register to join the discussion.
very roughly ...
Haven't tried it - just put the code together in 5 mins - but I think this is the jist of what you are trying to do
Private Sub Workbook_Open()
' -------------------------------------
' start process
' -------------------------------------
Application.OnTime Now + TimeValue("00:05:00"), "AutoResizeColumn"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' -------------------------------------
' cancel OnTime on close
' -------------------------------------
Application.OnTime dTime, "AutoResizeColumn", , False
End Sub
' -------------------------------------
' in standard module
' -------------------------------------
Public dTime as Date
Sub AutoResizeColumn()
' -------------------------------------
' resize
' -------------------------------------
dTime = Now + TimeValue("00:05:00")Application.OnTime dTime, "AutoResizeColumn"
Application.OnTime dTime, "AutoResizeColumn"
Dim oSheet As Excel.Worksheet
For Each oSheet In ActiveWorkbook.Worksheets
oSheet.Columns("B").EntireColumn.AutoFit
' -------------------------------------
' give other processes a look in
' -------------------------------------
DoEvents()
Next oSheet
End Sub
No doubt there are other ways of doing this but 'in theory' this should work; at least it will give a starting point
A VBA code for automatic Refresh in PV and formatting
JC,
Thank very much!
I will play with it and will let you know
A VBA code for automatic Refresh in PV and formatting
Hi JC,
I tried to test your code, but I have received an error.
The first code was Okay, i.e. the one that was created in "This Workbook",
But when I copied the second code in the "Standard module" I have received a compiled error (Syntax error, i.e. there are two red lines on this line of code)
dTime = Now + TimeValue("00:01:00")Application.OnTime dTime, "AutoResizeColumn"
I can also see another red line error on this line:
DoEvents()
When I tried to run the first line of code (This workbook), and hit the F5 key, it took me to the standard module page, and highlighted the above errors again.
Complied error: End of a statement
With my little knowledge of VBA, here is my guess.
As "OnTime" function is an event driven, it should go to either workbook, or worksheet, not to Standard modules. You can call (Use) it from standard module. Am I right?
I have few codes in standard modules I want to auto size the columns. If I want to use your code, what shall I do?
Thanks for your time!