+ Reply to Thread
Results 1 to 10 of 10

Changing macro that multiply current sheet

  1. #1
    Registered User
    Join Date
    05-24-2021
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    20

    Changing macro that multiply current sheet

    Hi

    I have ~100 sheets named as 1, 2, 3, 4,...

    Currently I use this macro to create copy of sheet "1" to 100 if I enter number 99 in InputBox
    Please Login or Register  to view this content.
    Now I have problem with changing this macro to copy active sheet name (lets say sheet "54") because I need to change macro every time. Is there way to macro use active sheet name and make copies untile number entered in InputBox+1 ?

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,688

    Re: Changing macro that multiply current sheet

    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,013

    Re: Changing macro that multiply current sheet

    even shorter
    Please Login or Register  to view this content.
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,688

    Re: Changing macro that multiply current sheet


  5. #5
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,013

    Re: Changing macro that multiply current sheet

    invalid attachment ?
    Indeed, I know that activesheet in #3 changes every loop to the last new added sheet.

  6. #6
    Registered User
    Join Date
    05-24-2021
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    20

    Re: Changing macro that multiply current sheet

    @jolivanes @bsalv

    Thats works but now problem is this part of code
    Please Login or Register  to view this content.
    If I leave this and try to run VBA from active sheet "10" I get new sheets from 2-9 and error when trying to create sheet with name 10.

    Can I use somehow Sheets(ActiveSheet.Name) as value instead number "1" in this part of code ?

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,688

    Re: Changing macro that multiply current sheet

    Like so?
    Please Login or Register  to view this content.
    Supply the total sheets wanted.
    Please Login or Register  to view this content.
    Last edited by jolivanes; 12-20-2021 at 12:56 PM. Reason: extra info

  8. #8
    Registered User
    Join Date
    05-24-2021
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    20

    Re: Changing macro that multiply current sheet

    First one is good but with new problem.

    Please Login or Register  to view this content.
    This part count sheet index but there is few hidden sheets so one solution is to add

    Please Login or Register  to view this content.
    or is there any way to covert ActiveSheet.Name to value and use that value in line above ?

  9. #9
    Valued Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,013

    Re: Changing macro that multiply current sheet

    here you have 100 sheets in ascending order, you can move or delete some sheets manually and run the macro "oder_so"
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by bsalv; 12-22-2021 at 05:10 PM.

  10. #10
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,688

    Re: Changing macro that multiply current sheet

    Or you could try like so also.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Enabling current macro to work on current sheet it resides
    By CHolden in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-24-2018, 01:12 PM
  2. Button caption changing depending on macro and current state
    By Darkprophecy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2015, 03:02 AM
  3. Addiction/Habit-Changing Sheet Current, Longest, Streaks of Consecutive Values
    By AllanTheCowboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-11-2015, 04:37 PM
  4. Changing reference sheet based on current quarter?
    By GarnetBlack32 in forum Excel General
    Replies: 2
    Last Post: 02-05-2012, 05:19 PM
  5. Multiply current month * current hour
    By TisorkA in forum Excel General
    Replies: 1
    Last Post: 01-11-2012, 02:12 PM
  6. Macro to sort an activity sheet by current week and current + last 1 and 2 weeks
    By engineering_excel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-13-2011, 11:28 AM
  7. [SOLVED] Changing a sheet name to the current date
    By Reddist in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2006, 03:40 PM

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