Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Thanked 1 Time in 1 Post

    Split worksheets (Excel xp)


    i've been using this macro to search a long file that has Questions in COL A. The questions are always Q.1 What is your name, Q.2a What is your age, Q.3 What is your weight etc.

    I want to split a long worksheet onto multiple worksheets using the "Q." as the key on when to start a new sheet. I am stuck on how to "name" the worksheet by the Question number. For instance, I'd like the sheet name to be Q.1, Q.2a, Q.3 or whatever precedes the question text "What is your name?" and starts with a "Q.". The question numbers could be 2 digits, Q.34a, Q.34b, Q.35...

    Here is what i've been using. Thank you for the help.

    Sub SplitWorksheets()

    Dim lngRows As Long
    Dim i As Integer
    Dim strTable As String
    Dim strQ As String
    Dim strValue As String

    lngRows = 15000
    Application.ScreenUpdating = False

    For i = 1 To lngRows
    If UCase$(Mid$(ActiveCell.Value, 1, 2)) = "Q." Then
    strQ = ActiveCell.Value
    ActiveCell.Offset(1, 0).Activate
    strTable = ActiveCell.Value
    Sheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = strTable
    ActiveCell.Range("A1").Value = strQ
    ActiveCell.Offset(1, 0).Activate
    Range("A" & ActiveCell.Row & ":Z" & ActiveCell.Row).Select
    ActiveCell.Offset(1, 0).Activate
    End If
    ActiveCell.Offset(1, 0).Activate
    Application.ScreenUpdating = True
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Split worksheets (Excel xp)

    Your code does more than your question is asking about. Do you want the code to what you ask or is there some other things it needs to do?

    You have the variable 'waveFile' undeclared. What is the variable and what does it contain.
    Also the variable strTable does not have a value

    Could you explain what you want the code to do? I assume that the current code does not do what you want or you wouldn't be asking for help.

    You do a lot of slelecting/activating which slows down the code and I would eliminate that.

    Perhaps you could attache a sample file with a before and after. it seems that you have more than just info in col A since you want to copy cols A to Z.

    I think I have an idea of what you want, but it would easier to code and test, if I had a sample.


Posting Permissions

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