+ Reply to Thread
Results 1 to 7 of 7

Trying to make a recorded macro work properly

  1. #1
    Registered User
    Join Date
    08-22-2012
    Location
    Bridgend, Wales
    MS-Off Ver
    Excel 2007
    Posts
    4

    Trying to make a recorded macro work properly

    Hello everyone

    Although I've been a member for a while, this is my first post.

    This is my problem. I have recorded a macro to pull data from an Excel worksheet into a pivot table on a new worksheet with defined formatting and layout.
    I also want the macro to rename the tabs of the data sheet and pivot table sheet. I want the macro to be able to be run no matter what the data sheet name be, "Sheet1", "Data", etc, it's only the 'new' name that is important, in this instance I want them to be titled, "Labour Hours" for the data sheet and "Hours Pivot" for the pivot.

    I have seen many comments here about wrapping code into tags, but I have no idea what that is so rather than make the same error, can someone tell me how to upload my recorded code. I have attached the data sheet to begin with.

    Any help you can give would be hugely appreciated.

    Many thanks in advance.

    Nick
    Attached Files Attached Files

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Trying to make a recorded macro work properly

    To upload your code, copy your code into your post window, then select it and press the # icon at the top of your post window.

    Alternatively, type [noparse][code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] after your code and that will input the code tags for you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    08-22-2012
    Location
    Bridgend, Wales
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Trying to make a recorded macro work properly

    Hi arlu

    Thank you very much for your guidance, here is the code I need help with ...

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Trying to make a recorded macro work properly

    Are you getting any error or do you just want to make it more efficient?

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Trying to make a recorded macro work properly

    Like this portion
    Please Login or Register  to view this content.
    can be replaced by

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-22-2012
    Location
    Bridgend, Wales
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Trying to make a recorded macro work properly

    Hi Arlette

    During my search for a fix I have seen that this recording is far from efficient, so in answer to your question, both please.

    My original error was a "Run Time Error 9: Subscript out of range" which in my very limited knowledge I thought is due to it was referencing the name of the sheet in which I created the macro.

    I suppose I have a number of issues;

    next to no knowledge of macros
    I want the macro to run in the active worksheet no matter what it's named
    I want the macro to rename the data sheet and pivot sheet as per my original post
    I would like to be as efficient as possible so I can learn from it for the future
    I don't know where to start

  7. #7
    Registered User
    Join Date
    08-22-2012
    Location
    Bridgend, Wales
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Trying to make a recorded macro work properly

    Thanks to all who contributed here. I eventually solved this through trial and error, and referencing lots of www sites, so thanks to all the contributors and sources out there; I couldn't have done it without you.

+ 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