Results 1 to 2 of 2
Thread: Split worksheets (Excel xp)
2004-08-03, 22:16 #1
- 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.
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
strTable = ActiveCell.Value
ActiveSheet.Name = strTable
ActiveCell.Range("A1").Value = strQ
Range("A" & ActiveCell.Row & ":Z" & ActiveCell.Row).Select
Application.ScreenUpdating = True
2004-08-04, 02:28 #2
- 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.