+ Reply to Thread
Results 1 to 3 of 3

Macro to insert a worksheet into a workbook, rename the work sheet and copy and past data

  1. #1
    Registered User
    Join Date
    09-03-2012
    Location
    Laurel, Maryland
    MS-Off Ver
    Excel 2007
    Posts
    1

    Post Macro to insert a worksheet into a workbook, rename the work sheet and copy and past data

    Hello Friends,

    I am trying to save some steps in my tasks. I need to create a macro that will add a worksheet to a workbook rename the worksheet and then copy data from a pivot table to that worksheet. I have used the Macro recorder to do this. However when I run the macro I get a run time error because the name of the sheet is static in the code and so I think the code is looking for specific sheet. How can I revise it s that it rename whatever the next numbered worksheet is that is added? Please help. It seems this should be pretty simple but I have been banging my head for a couple of days now. Here is the code I currently have:


    Please Login or Register  to view this content.

    How do I change the code to read instead of "Sheet27" something like Lastsheet so it is looking for the last numbered sheet added not a specific number - this is where the macro times out.

    Thanks in advance for your help.

    Kwals

  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: Macro to insert a worksheet into a workbook, rename the work sheet and copy and past

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.
    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
    Forum Contributor
    Join Date
    09-29-2011
    Location
    Kolkata, India
    MS-Off Ver
    Excel 2003/2007
    Posts
    182

    Re: Macro to insert a worksheet into a workbook, rename the work sheet and copy and past

    Hi Kwals,

    Welcome to Excel Forum.


    You can use ..
    Please Login or Register  to view this content.
    Last edited by Cutter; 09-03-2012 at 02:28 PM. Reason: Added code tags

  4. #4
    Registered User
    Join Date
    11-05-2008
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    67

    Re: Macro to insert a worksheet into a workbook, rename the work sheet and copy and past

    Hiya Qwals,

    This is a modified version of what I use to duplicate a sheet a daily basis.

    Please Login or Register  to view this content.
    I use this code below to name the sheet with a weekday (just to give you some ideas if you need something that will give you a new sheet per day
    Please Login or Register  to view this content.
    I trust this helps

    Iain
    Last edited by sarails; 09-04-2012 at 01:25 AM.

+ 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