I am trying to create a Macro linked to an Excel button from the forms
toolbar in cell N1 of Sheet 1. I would like it to perform the following
steps.

1. Copy the entire contents of rows in Sheet 1 from A2 to L30.

2. Sort all the contents of rows A through L based on the acronyms AAAA,
BBBB, CCCC, DDDD, & EEEE and group the AAAA with the AAAA, the BBBB with the
BBBB, and so on. (The acronyms are located in Column F rows F2 through F30).

3. Create and name a new worksheet for all the AAAA, BBBB, CCCC, DDDD, &
EEEE.

4. Paste all the contents of rows A through L containing AAAA in Sheet
AAAA, all the contents of the rows A through L containing BBBB in Sheet BBBB,
all the contents of rows A through L containing CCCC in Sheet CCCC, and so
on.
Do you have any ideas about how I might make that happen?

1. *Copy the entire contents of rows in Sheet 1 from A2 to L30.

Copy the entire contents of rows in Sheet 1 from which Sheet ?

Sub Sort_Acronyms()

With Sheets("sheet1")
Set ShortSht = Worksheets.Add(after:=Sheets(Sheets.Count))
ShortSht.Name = "Sort Data"
.Range("A2:L30").Copy Destination:=ShortSht.Range("A1")
End With
With ShortSht
.Range("A1:L29").Sort _
Key1:=Range("F1"), _
Header:=xlNo

RowCount = 1
FirstRow = RowCount
Do While RowCount <= 30
If .Range("F" & RowCount) < .Range("F" & (RowCount + 1)) Then
Set NewSht = Worksheets.Add(after:=Sheets(Sheets.Count))
NewSht.Name = .Range("F" & RowCount)
.Rows(FirstRow & ":" & RowCount).Copy _
Destination:=NewSht.Rows(1)
FirstRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End With

End Sub


"ytayta555" wrote:

1. Copy the entire contents of rows in Sheet 1 from A2 to L30.

Copy the entire contents of rows in Sheet 1 from which Sheet ?

Thank you for your feedback. I am not sure what is happening. The first
time I click the Excel button from the forms toolbar I linked to the Macro it
jumps to the Visual Basic Editor and says "Compile error, expected end sub"
if I go back to the worksheet and click the Excel button again it jumps to
the VBE again and says "Can't execute code in break mode". Do you have any
thoughts?

"Joel" wrote:

Sub Sort_Acronyms()

With Sheets("sheet1")
Set ShortSht = Worksheets.Add(after:=Sheets(Sheets.Count))
ShortSht.Name = "Sort Data"
.Range("A2:L30").Copy Destination:=ShortSht.Range("A1")
End With
With ShortSht
.Range("A1:L29").Sort _
Key1:=Range("F1"), _
Header:=xlNo

RowCount = 1
FirstRow = RowCount
Do While RowCount <= 30
If .Range("F" & RowCount) < .Range("F" & (RowCount + 1)) Then
Set NewSht = Worksheets.Add(after:=Sheets(Sheets.Count))
NewSht.Name = .Range("F" & RowCount)
.Rows(FirstRow & ":" & RowCount).Copy _
Destination:=NewSht.Rows(1)
FirstRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End With

End Sub


"ytayta555" wrote:

1. Copy the entire contents of rows in Sheet 1 from A2 to L30.

Copy the entire contents of rows in Sheet 1 from which Sheet ?

Make sure you copied the entire macro from SUB.... to End Sub. You can
check for compiler errors ( I just did with the posted code and there aren't
any) by going to VBA Debug menu and selecting Compile. The code stoped when
it found an error. You can get out of the Break Mode by going to VBA Run
menu and selecting Reset.

"MSE" wrote:

Thank you for your feedback. I am not sure what is happening. The first
time I click the Excel button from the forms toolbar I linked to the Macro it
jumps to the Visual Basic Editor and says "Compile error, expected end sub"
if I go back to the worksheet and click the Excel button again it jumps to
the VBE again and says "Can't execute code in break mode". Do you have any
thoughts?

"Joel" wrote:

Sub Sort_Acronyms()

With Sheets("sheet1")
Set ShortSht = Worksheets.Add(after:=Sheets(Sheets.Count))
ShortSht.Name = "Sort Data"
.Range("A2:L30").Copy Destination:=ShortSht.Range("A1")
End With
With ShortSht
.Range("A1:L29").Sort _
Key1:=Range("F1"), _
Header:=xlNo

RowCount = 1
FirstRow = RowCount
Do While RowCount <= 30
If .Range("F" & RowCount) < .Range("F" & (RowCount + 1)) Then
Set NewSht = Worksheets.Add(after:=Sheets(Sheets.Count))
NewSht.Name = .Range("F" & RowCount)
.Rows(FirstRow & ":" & RowCount).Copy _
Destination:=NewSht.Rows(1)
FirstRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End With

End Sub


"ytayta555" wrote:

1. Copy the entire contents of rows in Sheet 1 from A2 to L30.

Copy the entire contents of rows in Sheet 1 from which Sheet ?

I made sure to copy the entire macro from SUB... to End Sub. I checked for
compiler errors by going to VBA Debug menu and selecting Compile. A message
came up that said "Compile error: Expected End Sub" do you have any thoughts
about why I might get that message? Also, I followed the suggestion to get
out of Break Mode by going to VBA Run menu and selecting Reset, however I am
getting the same messages I mentioned earlier, i.e. "Compile error, expected
end sub"
and "Can't execute code in break mode" when I try to use the Excel button,
do you have any other thoughts about what else might be causing the error?

"Joel" wrote:

Make sure you copied the entire macro from SUB.... to End Sub. You can
check for compiler errors ( I just did with the posted code and there aren't
any) by going to VBA Debug menu and selecting Compile. The code stoped when
it found an error. You can get out of the Break Mode by going to VBA Run
menu and selecting Reset.

"MSE" wrote:

Thank you for your feedback. I am not sure what is happening. The first
time I click the Excel button from the forms toolbar I linked to the Macro it
jumps to the Visual Basic Editor and says "Compile error, expected end sub"
if I go back to the worksheet and click the Excel button again it jumps to
the VBE again and says "Can't execute code in break mode". Do you have any
thoughts?

"Joel" wrote:

Sub Sort_Acronyms()

With Sheets("sheet1")
Set ShortSht = Worksheets.Add(after:=Sheets(Sheets.Count))
ShortSht.Name = "Sort Data"
.Range("A2:L30").Copy Destination:=ShortSht.Range("A1")
End With
With ShortSht
.Range("A1:L29").Sort _
Key1:=Range("F1"), _
Header:=xlNo

RowCount = 1
FirstRow = RowCount
Do While RowCount <= 30
If .Range("F" & RowCount) < .Range("F" & (RowCount + 1)) Then
Set NewSht = Worksheets.Add(after:=Sheets(Sheets.Count))
NewSht.Name = .Range("F" & RowCount)
.Rows(FirstRow & ":" & RowCount).Copy _
Destination:=NewSht.Rows(1)
FirstRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End With

End Sub


"ytayta555" wrote:

1. Copy the entire contents of rows in Sheet 1 from A2 to L30.

Copy the entire contents of rows in Sheet 1 from which Sheet ?

Copy the entire contents of rows 2 through 30 columns A through L in Sheet 1
from Sheet 1 using an Excel button from the forms toolbar in Cell N1 on Sheet
1. I want to have the results appear in new worksheets. Does that make my
question clearer? If not please be sure to let me know. Thank you for your
help.

"ytayta555" wrote:

1. Copy the entire contents of rows in Sheet 1 from A2 to L30.

Copy the entire contents of rows in Sheet 1 from which Sheet ?