Next Without for Compile Error when running loop

aknox6

New Member
Joined
Sep 25, 2017
Messages
6
See what I have come up with below. What I am trying to do, is take data from a tab insert it into a separate worksheet (Appendix A - this is a template so it has to be inserted in a certain spot), and then save that as the Cell Name (E4), and have it rerun through the other tabs in the worksheet and do the same. So the end Product would be a bunch of Appendix A's using data from separate tabs in the original work book (Award Tempate). I was running it fine earlier and would get caught up on the save portion of the loop, but now it is telling me "Compile Error End without for". However, I am not sure what this is referring to since I have a For in the beginning. I am new to VBA, so any help would be appreciated. I have been stuck on this for going on 8 hours now...:mad:

Sub LoopTest()
'Loop - Appendix A Workbook must also be open for this to run
Dim First As Integer, Last As Integer, i As Integer
First = Sheets("AAAA").Index
Last = Sheets("ZZZZ").Index
For i = First + 1 To Last - 1
With Sheets(i)
'Copies data from Award Template Workbook and Paste into Appendix A Workbook
Range("A2").Select
Selection.Copy
Windows("Appendix A.xlsm").Activate
ActiveSheet.Paste
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
Selection.Font.Size = 10
Selection.Font.Size = 9
Windows("zAward Template - Test - Copy.xlsm").Activate
Range("C2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Appendix A.xlsm").Activate
Range("A15").Select
Selection.Insert Shift:=xlDown
Windows("zAward Template - Test - Copy.xlsm").Activate
ActiveWindow.SmallScroll Down:=-3
Windows("Appendix A.xlsm").Activate
ActiveWindow.SmallScroll Down:=12
'Saves the copied cells in the Appendix A as a New Workbook with the Name being a Cell Value (E4)
Dim strPath As String
Dim strFolderPath As String
strFolderPath = "U:"
strPath = strFolderPath & Sheet1.Range("E4").Value & ".xlsm"

With ActiveWorkbook
ActiveWorkbook.SaveAs Filename:=strPath
'Reopens Appendix A so the Macro can rerun through the loop without overwriting the previous data
ChDir "C:\Users\aknox\Desktop\LSS\CD\Macro"
Workbooks.Open Filename:= _
"C:\Users\aknox\Desktop\LSS\CD\Macro\Appendix A.xlsm"

End With

Next i
End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
See what I have come up with below. What I am trying to do, is take data from a tab insert it into a separate worksheet (Appendix A - this is a template so it has to be inserted in a certain spot), and then save that as the Cell Name (E4), and have it rerun through the other tabs in the worksheet and do the same. So the end Product would be a bunch of Appendix A's using data from separate tabs in the original work book (Award Tempate). I was running it fine earlier and would get caught up on the save portion of the loop, but now it is telling me "Compile Error End without for". However, I am not sure what this is referring to since I have a For in the beginning. I am new to VBA, so any help would be appreciated. I have been stuck on this for going on 8 hours now...:mad:

Sub LoopTest()
'Loop - Appendix A Workbook must also be open for this to run
Dim First As Integer, Last As Integer, i As Integer
First = Sheets("AAAA").Index
Last = Sheets("ZZZZ").Index
For i = First + 1 To Last - 1
With Sheets(i)
'Copies data from Award Template Workbook and Paste into Appendix A Workbook
Range("A2").Select
Selection.Copy
Windows("Appendix A.xlsm").Activate
ActiveSheet.Paste
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
Selection.Font.Size = 10
Selection.Font.Size = 9
Windows("zAward Template - Test - Copy.xlsm").Activate
Range("C2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Appendix A.xlsm").Activate
Range("A15").Select
Selection.Insert Shift:=xlDown
Windows("zAward Template - Test - Copy.xlsm").Activate
ActiveWindow.SmallScroll Down:=-3
Windows("Appendix A.xlsm").Activate
ActiveWindow.SmallScroll Down:=12
'Saves the copied cells in the Appendix A as a New Workbook with the Name being a Cell Value (E4)
Dim strPath As String
Dim strFolderPath As String
strFolderPath = "U:"
strPath = strFolderPath & Sheet1.Range("E4").Value & ".xlsm"

With ActiveWorkbook
ActiveWorkbook.SaveAs Filename:=strPath
'Reopens Appendix A so the Macro can rerun through the loop without overwriting the previous data
ChDir "C:\Users\aknox\Desktop\LSS\CD\Macro"
Workbooks.Open Filename:= _
"C:\Users\aknox\Desktop\LSS\CD\Macro\Appendix A.xlsm"

End With

Next i
End Sub
You have two With statements that do not have End With statements to close off the With blocks (see red highlight above). One problem with your first With block is than none of the cell references in the rest of your code have a dot in front of them meaning that no matter what worksheet you think you will be referencing, you code would always reference the active sheet.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top