Macro Code Help with Ranges & Sheets

Hi,

I have a workbook with several worksheets that have data that is recorded weekly. I created a pivot table to sum up the data I need to record in a grand total per person sheet. After I create the pivot table and double click on a persons information it opens up an entire new sheet (example sheet1) What I wanted to do is create a macro that did all the work for me. However the problem I am having is every time I double click a new person information from my pivot table it names the new sheet the next number in line (example sheet2,sheet3) ect... I need a way for the sheet to stay the same name when I run my macro.

Can someone guide me to a solution? Here is my code I have so far.

A few problems I am having are as follows:


1) Every time I click my button macro it pulls the same persons data regardless of where the cursor is at.The first person is at "b3" but they can go down as far as "b50".

2) You can see it always references (sheet1) I need it to always reference the new sheet that was just created at the beginning of the macro.

3) I would like this sheet deleted without asking if I was sure I wanted to delete it.

4) Lastly I want it to return to the first sheet and select the next cell down, Problem 1 always takes cell "b3" I would like the macro to pick for example b4 and so on each time it runs till It runs out of data.

Please Login or Register  to view this content.

Thanks for any help someone can provide.

Mike

1. Hard to say without seeing all the code, but it looks like you can just delete the first line and use the current selection.

2.

Please Login or Register  to view this content.

3.

Please Login or Register  to view this content.

4. I don't understand the question.

As an aside, it is almost never necessary to select a sheet to operate on its data.

Thank You very much for your advice. Everything almost works perfectly.I wasnt sure to post questions again in this thread.Or start a new one. I guess you will let me know.

Only two problems left:
The New One:

Let me give you just a bit more information. The workbook I am working in has 2 sheets one is called "data" the other is "New Totals" The data sheet is a pivot table, When my macro runs it double clicks on cell "b3" Which opens up a new sheet#, It then copies a group of cells. Then it is suppose to paste special into the New Totals sheet. (It will paste however only zero's) After the paste the new sheet# should delete.

The Second One:

What I would like to see happen is after the (sheet#) is deleted it automaticly return to the data sheet for "B4" then run macro again and again like a loop, I need the macro to add one cell down each time it runs (Example) 1st Time Run =b3, 2nd b4 ,3rd b5 and so on all the down till it hits an empty cell.


Anyway here is my new code:

Please Login or Register  to view this content.

Thanks Again for your help:

I can't tell what you're doing, so this is, per force, untested:

Please Login or Register  to view this content.

Thank You again. We are getting closer to correcting the problem. Here is what is happening now with your changes to my code.


When I click my macro button 27 new sheets open up at one time. Each sheet contains the data I am looking for, However it appear to only and paste the first sheets data,and they are only zero's .It looks like it may be zero's because it is not inserting the new lines for the caluclation to be done? also the sheets Are not deleting after the macro runs?

Here is what I am trying to do:

I have 2 sheets one "data" and "New Totals" The "data" sheet is a pivot table When I click my macro button it is suppose to open up a new sheet insert 3 lines Do the calculations from the list copy the selection then paste special to the new totals sheet. Then delete the new sheet that was opened up then go back to the data sheet and select the next cell down and go through the entire process again till it reachs an empty cell.

Please Login or Register  to view this content.

Once again thank you for any help with this project: