If first letter of work begins with ......

  • Thread starter Thread starter That's Confidential
  • Start date Start date
T

That's Confidential

In column A of 12 sheets, I have a list of surnames. Now what I want to do
is to copy all the surnames within column A of the 12 sheets beginning with
A-H into a new sheet, and then all the surnames beginning with I-O in the
columns in the 12 sheets into a new sheet, and then all the surnames
beginning with P-Z into another sheet!

Any ideas?
 
You would need a macro such as the following (completely untested):

Sub Summarize()
Worksheets.Add.Name "P-Z"
Worksheets.Add.Name = "I-O"
Worksheets.Add.Name = "A-H"
Dim ws As Worksheet, c As Range
For Each ws In Worksheets
If ws.Name <> "A-H" And ws.Name <> "I-O" _
And ws.Name <> "P-Z" Then
For Each c In Intersect(ws.Range("A:A"), ws.UsedRange)
If c <> "" Then
If Asc(UCase(Left(c, 1))) > 79 Then
c.EntireRow.Copy Worksheets("P-Z").UsedRange(1, 1) _
.Offset(1).EntireRow
ElseIf Asc(UCase(Left(c, 1))) > 72 Then
c.EntireRow.Copy Worksheets("I-O").UsedRange(1, 1) _
.Offset(1).EntireRow
Else
c.EntireRow.Copy Worksheets("A-H").UsedRange(1, 1) _
.Offset(1).EntireRow
End If
Next
End If
Next
End Sub
 
I have tried this Macro, however every time I run it, I get a message back
saying "Compile error: Next without For......and then it highlights the word
"Next" at the bottom of the Code

Any help?
 
Hi
also not tested but Vasant missed one End if. Try:
Sub Summarize()
Worksheets.Add.Name "P-Z"
Worksheets.Add.Name = "I-O"
Worksheets.Add.Name = "A-H"
Dim ws As Worksheet, c As Range
For Each ws In Worksheets
If ws.Name <> "A-H" And ws.Name <> "I-O" _
And ws.Name <> "P-Z" Then
For Each c In Intersect(ws.Range("A:A"), ws.UsedRange)
If c <> "" Then
If Asc(UCase(Left(c, 1))) > 79 Then
c.EntireRow.Copy Worksheets("P-Z").UsedRange(1,
1) _
.Offset(1).EntireRow
ElseIf Asc(UCase(Left(c, 1))) > 72 Then
c.EntireRow.Copy Worksheets("I-O").UsedRange(1,
1) _
.Offset(1).EntireRow
Else
c.EntireRow.Copy Worksheets("A-H").UsedRange(1,
1) _
.Offset(1).EntireRow
End If
End If
Next
End If
Next
End Sub
 
try adding another end if
BTW. you could have waited just a little longer before saying that "no-one
could help me" on your other post.
 
It's coming back with a "Compile Syntax Error" at c.EntireRow.Copy
Worksheets("P-Z").UsedRange(1,
 
Sorry Don!

As this NG is a worksheet.functions one, I presumed it would be likely that
nobody would be able to help me!

Fair comment though!
 
Should I already have created the extra sheets "A-H" "I-O" and "P-Z", or
does the code create them for me?
 
Sorry, here's a better version (lightly tested this time):

Sub Summarize()
Worksheets.Add.Name = "P-Z"
Worksheets.Add.Name = "I-O"
Worksheets.Add.Name = "A-H"
Dim ws As Worksheet, c As Range
For Each ws In Worksheets
If ws.Name <> "A-H" And ws.Name <> "I-O" _
And ws.Name <> "P-Z" Then
For Each c In Intersect(ws.Range("A:A"), ws.UsedRange)
If c <> "" Then
If Asc(UCase(Left(c, 1))) > 79 Then
c.EntireRow.Copy Worksheets("P-Z").UsedRange _

..Offset(Worksheets("P-Z").UsedRange.Rows.Count).EntireRow
ElseIf Asc(UCase(Left(c, 1))) > 72 Then
c.EntireRow.Copy Worksheets("I-O").UsedRange _

..Offset(Worksheets("I-O").UsedRange.Rows.Count).EntireRow
Else
c.EntireRow.Copy Worksheets("A-H").UsedRange _

..Offset(Worksheets("A-H").UsedRange.Rows.Count).EntireRow
End If
End If
Next
End If
Next
End Sub
 
Put the 1)_ that is in the next line in this thread after the ,

You need to do this for each of the 3 lines that overwrapped.


: It's coming back with a "Compile Syntax Error" at c.EntireRow.Copy
: Worksheets("P-Z").UsedRange(1,
:
:
: : > Hi
: > also not tested but Vasant missed one End if. Try:
: > Sub Summarize()
: > Worksheets.Add.Name "P-Z"
: > Worksheets.Add.Name = "I-O"
: > Worksheets.Add.Name = "A-H"
: > Dim ws As Worksheet, c As Range
: > For Each ws In Worksheets
: > If ws.Name <> "A-H" And ws.Name <> "I-O" _
: > And ws.Name <> "P-Z" Then
: > For Each c In Intersect(ws.Range("A:A"), ws.UsedRange)
: > If c <> "" Then
: > If Asc(UCase(Left(c, 1))) > 79 Then
: > c.EntireRow.Copy Worksheets("P-Z").UsedRange(1,
: > 1) _
: > .Offset(1).EntireRow
: > ElseIf Asc(UCase(Left(c, 1))) > 72 Then
: > c.EntireRow.Copy Worksheets("I-O").UsedRange(1,
: > 1) _
: > .Offset(1).EntireRow
: > Else
: > c.EntireRow.Copy Worksheets("A-H").UsedRange(1,
: > 1) _
: > .Offset(1).EntireRow
: > End If
: > End If
: > Next
: > End If
: > Next
: > End Sub
: >
: > --
: > Regards
: > Frank Kabel
: > Frankfurt, Germany
: >
: >
: > That's Confidential wrote:
: > > I have tried this Macro, however every time I run it, I get a message
: > > back saying "Compile error: Next without For......and then it
: > > highlights the word "Next" at the bottom of the Code
: > >
: > > Any help?
: > >
: > > Vasant Nanavati <vasantn *AT* aol *DOT* com> wrote in message
: > > : > >> You would need a macro such as the following (completely untested):
: > >>
: > >> Sub Summarize()
: > >> Worksheets.Add.Name "P-Z"
: > >> Worksheets.Add.Name = "I-O"
: > >> Worksheets.Add.Name = "A-H"
: > >> Dim ws As Worksheet, c As Range
: > >> For Each ws In Worksheets
: > >> If ws.Name <> "A-H" And ws.Name <> "I-O" _
: > >> And ws.Name <> "P-Z" Then
: > >> For Each c In Intersect(ws.Range("A:A"), ws.UsedRange)
: > >> If c <> "" Then
: > >> If Asc(UCase(Left(c, 1))) > 79 Then
: > >> c.EntireRow.Copy
: > >> Worksheets("P-Z").UsedRange(1, 1) _
: > >> .Offset(1).EntireRow ElseIf Asc(UCase(Left(c,
: > >> 1))) > 72 Then c.EntireRow.Copy
: > >> Worksheets("I-O").UsedRange(1, 1) _
: > >> .Offset(1).EntireRow Else
: > >> c.EntireRow.Copy
: > >> Worksheets("A-H").UsedRange(1, 1) _
: > >> .Offset(1).EntireRow End If
: > >> Next
: > >> End If
: > >> Next
: > >> End Sub
: > >>
: > >> --
: > >>
: > >> Vasant
: > >>
: > >>
: > >>
: > >>
: > >>
: > >> : > >>> In column A of 12 sheets, I have a list of surnames. Now what I
: > >>> want to do is to copy all the surnames within column A of the 12
: > >>> sheets beginning with A-H into a new sheet, and then all the
: > >>> surnames beginning with I-O in the columns in the 12 sheets into a
: > >>> new sheet, and then all the surnames beginning with P-Z into
: > >>> another sheet!
: > >>>
: > >>> Any ideas?
: >
:
:
 
Back
Top