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...
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
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