Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
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 ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
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 ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
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 ? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
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 ? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
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 ? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
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 ? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
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 ? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
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 ? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
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 ? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
I think you need to do this
from Sub Button1_Click() Sub Sort_Acronyms() With Sheets("sheet1") |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acronym Macro
Sorry for any communication errors on my part. It works now! I am so
grateful for your help and patience. This Macro is amazing. Thank you! "Joel" wrote: I think you need to do this from Sub Button1_Click() Sub Sort_Acronyms() With Sheets("sheet1") . . '<the rest of your code . end Sub This fix will allow you to run the macro from button 1 or from the Menu Tool - Macro - Sort_Acronyms Sub Button1_Click() call Sort_Acronyms end sib Sub Sort_Acronyms() With Sheets("sheet1") . . '<the rest of your code . end Sub Or you can just get rid of the "Sub Sort_Acronyms()" statement a call the function from the Button. You didn't dpecify originally that you were trying to run the code from a button. Sorry. "MSE" wrote: 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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Acronym (Extract first letter of each word) | Excel Worksheet Functions | |||
Removing Of/And/The from Acronym Function | Excel Worksheet Functions | |||
Acronym Function? | Excel Worksheet Functions | |||
ACRONYM OF CAPS ONLY | Excel Discussion (Misc queries) | |||
How do I count a named range for a specific word or acronym? | Excel Worksheet Functions |