I think I see what the problem is but I am not sure how to fix it. I have 2
Subs in the first two lines of the code (once I add the Excel button from the
forms toolbar.) I posted an example below to give you a better idea of what
I mean. When I click the Excel button, in addition to the message boxes I
told you have been popping up, Sub Button 1_Click() becomes yellow. A yellow
arrow also appears pointing next to it. I am using Excel 2003, just in case
that makes a difference.
Sub Button1_Click()
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
"Joel" wrote:
> Here is the code again. Make sure yu have everything between the lines. Try
> opening a new workbook and adding the code just to verify the code is good.
> It is possible if you have other macro in the workbook that the other macros
> are causing the error.
>
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> 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
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>
> "MSE" wrote:
>
> > When the message came up that said "Compile error: Expected End Sub" I had
> > the option to select OK or Help. I choose help and got the following
> > message.... (Perhaps it might bring clarity to my question).
> >
> > You enter break mode when you suspend execution of code. This error has the
> > following causes and solutions:
> > • You tried to run code from the Macro dialog box. However, Visual Basic was
> > already running code, although the code was suspended in break mode.
> > You may have entered break mode without knowing it, for example, if a syntax
> > error or run-time error occurred. Continue running the suspended code, or
> > terminate its execution before you run code from the Macro dialog box. You
> > can fix the error and choose Continue, or you can return to the Macro dialog
> > box and restart the macro.
> > For additional information, select the item in question and press F1 (in
> > Windows) or HELP (on the Macintosh).
> >
> >
> > "MSE" wrote:
> >
> > > 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 ?
> > > > > > >
|
Similar Threads
