Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Split worksheets (Excel xp)

    Hi,

    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
    Range("A9").Select

    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)
    ActiveCell.Range("a1").Select
    ActiveSheet.Name = strTable
    ActiveCell.Range("A1").Value = strQ
    ActiveCell.Offset(1, 0).Activate
    Else
    Range("A" & ActiveCell.Row & ":Z" & ActiveCell.Row).Select
    Selection.Copy
    Sheets(strTable).Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Activate
    End If
    Sheets(waveFile).Select
    ActiveCell.Offset(1, 0).Activate
    Next
    Application.ScreenUpdating = True
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    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.

    Steve

Posting Permissions

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