+ Reply to Thread
Results 1 to 5 of 5

Macro Code Help with Ranges & Sheets

  1. #1
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    PA
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    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

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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.

  3. #3
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    PA
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951
    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:

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I can't tell what you're doing, so this is, per force, untested:
    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    PA
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951
    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:

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1