Automatically refresh a dynamic table

[rodroll]

I am not an basic excel user but I know nothing about VBA and macros, so my question may sound silly.
I was finishing a pivot table and wanted the table to refresh automatically, I searched the net for a solution and followed this suggestion:

"Right click the tab of the worksheet that contains your source data and pivot table and select View Code. This will present you with a code module which is associated with this particular sheet. Type this:

Private Sub Worksheet_Change(ByVal Target As Range)
Me.PivotTables(1).RefreshTable
End Sub

It assumes that there is only one pivot table on your worksheet."

I did that and did not like the result. So I wanted to delete this code.
And now it starts the problem.
I managed to succesfully delete the middle line, but the first and the third are still there.
In the microsoft visual basic, when "general" is select on the top of the window in the dropdown menu, there is no code, but when I select worksheet, I can see the code. And the code apeears in every worksheet of every file that I open.
Does anybody know why this is happening, and how I can delete these 2 lines of code?

Thanks In advance.

Rod

[Roger Govier]

Hi Rod

I cannot understand why the code is being repeated everywhere. Are you sure?
Perhaps you are not clicking on different sheets within the Project Explorer of the VB Editor, and it is the same code you are seeing all the time.

If you right click on the sheet tab and choose View Code, you will be looking at the sheet code for that particular sheet. You can then select any lines of any sub that is there, right click and choose Cut - that will get rid of the code.

The code you were trying to use is wrong anyway.
If you have the PT and source data on the same sheet, and you want the PT to update automatically as you alter any values in the source data, then you should switch off Enable Events first, then switch it back on again. Equally the line for refreshing the PT is incorrect.

Try the code below

Please Login or Register  to view this content.

For your information for future postings, any code should be included within the CODE tags so it appears as in my post.

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[rodroll]

Hi Roger,

I have no idea what happened, I opened the files in another computer and the VBA dissapeared, then I reinstalled excel in my computer and now the VBA also disappeared.
Thank you for the code, I just introduce it in my spreadsheet, and it is working the way I wanted. Thanks a lot.

Sorry about not using the codetags arround the code, it won't happen again.

Cheers,

Rod

[royUK]

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc