1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro - Multiple sheets

Discussion in 'Business Applications' started by firefox, May 7, 2003.

Thread Status:
Not open for further replies.
  1. firefox

    firefox Thread Starter

    Joined:
    May 7, 2003
    Messages:
    1
    Give me a hand to impress the boss!

    I have a column which has a list of different values. For each value in this column i wish to create a new sheet and name the sheet the same name as the value in the column.

    For this i have set up a do/while loop , however, after about 27 turns, it stops. :mad:

    My guess is that it is something to do with the sheet Name property, or the number of sheets that are there. i.e. in the VBA project box on the code screen, it appears like this
    Microsoft excel objects
    -sht1(main)
    -sht11(value)
    -sht111(value1)
    -sht1111(value2)
    etc until
    -sht111111111111111111111111(valuex)

    Here is a summary of the loop;

    Dim strValue1, strValue2 As String

    Do While intCounter < intNumSheets
    strValue1 = ActiveSheet.Range("E" & intCounter).Value
    strValue2 = ActiveSheet.Range("E" & intCounter + 1).Value

    If strValue1 <> strValue2 Then
    Application.CutCopyMode = False
    ActiveSheet.Copy After:=ActiveSheet
    ActiveSheet.Name = ActiveSheet.Range("E" & intCounter + 1).Value
    End If
    intCounter = intCounter + 1
    Loop

    Email me
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Yep. Bad news when that occurs. After while, you won't be able to open the workbook. You must be using 97, huh?

    Each time you make a copy of a copy, it adds the one.

    Try doing it this way instead:

    Create yourself a sheet you'll use as a template.
    Copy ONLY that sheet each time you create a new sheet.
    You can hide the template sheet.

    Don't ask me for the code, LOL!
    I don't code, but I can troubleshoot it pretty well. I know that makes no sense...
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/132367

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice