Excel Macro Problems

**realniceguy5000** · 03-20-2008, 01:23 PM

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) Everytime I click my button macro it pulls the same persons data reguardless 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 referances (sheet1) I need it to always referance the new sheet that was just created at the begining 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.

    Range("B3").Select		(Problem 1)			
    Selection.ShowDetail = True
    Rows("1:3").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[4]C"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=COUNT(R[4]C:R[499]C)"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[4]C:R[499]C)"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:F1"), Type:=xlFillDefault
    Range("D1:F1").Select
    Range("A1:F1").Select
    Selection.Copy
    Sheets("New Totals").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,   SkipBlanks _
        :=False, Transpose:=False
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    Sheets("Sheet1").Select			(Problem 2)
    ActiveWindow.SelectedSheets.Delete	(Problem 3)		
    Range("B4").Select			(Problem 4)			
End Sub

Thanks for any help somone can provide.
Mike

**Leith Ross** · 03-20-2008, 01:50 PM

Hello realniceguy5000,

Welcome to the Forum!

To make your posts easier to read and preserve your formatting, please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
On the Message window Toolbar you will see the # icon. This will automatically wrap the text you selected with the proper HTML tags to create a Code Window in your post. You can do this manually by placing the tag [code] at the start of the line, and the tag [/code] at the end.

As a new member please read the Do's and Don'ts here in the Forum. Click on the link below...

Forum Rules

Sincerely,
Leith Ross