I have two different workbooks that I am utilizing for this macro.
1.) "zAward Template - Test - Copy"
2.) Appendix A
I have written a macro that essentially takes the data I need in the template file from a tab and paste what is necessary to populate the appendix, does lookups to data, saves, and then reopens the Appendix A file so the previous information is not over written.
I have a For Each loop that is supposed to go through every tab in the worksheet with the exception of (Award, Appendix A, and Lookup). The problem here is that the macro does what it is supposed to do with the tab it starts on, then when it goes to loop again it repeats the same actions on the same page and I get an error that I can not save the same file name in the same file spot due to the fact that it is not moving on to the next worksheet.
I can not specify which tabs / how many tabs for it to loop through because it will vary depending on which project I am working on.
See below for the VBA I have so far. Do you see why it would not be looping onto the next sheet?
Sub LoopTest6()
'Loop - Appendix A Workbook must also be open for this to run
Dim ws As Worksheet
Windows("zAward Template - Test - Copy.xlsm").Activate
For Each ws In ActiveWorkbook.Worksheets
If (ws.name <> "Award") And (ws.name <> "Appendix A") And (ws.name <> "Lookup") Then
With ws
'Copies data from Award Template Workbook and Paste into Appendix A Workbook
' copypaste9261 Macro
Windows("zAward Template - Test - Copy.xlsm").Activate
Range("A2").Select
Selection.Copy
Windows("Appendix A.xlsm").Activate
Range("E4").Select
ActiveSheet.Paste
Rows("4:4").Select
Selection.EntireRow.Hidden = True
'copypaste9692() copies C:K from award file and paste into template
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
ActiveWindow.SmallScroll Down:=0
'Saves the copied cells in the Appendix A as a New Workbook with the Name being a Cell Value (E4)
Dim name As String
carrier = Range("E4").Value
name = Range("E3").Value
appa = Range("E1").Value
ActiveWorkbook.SaveAs Filename:="U:" & carrier & name & appa & ".xlsm", FileFormat:=52
ChDir "U:\Macro"
'Reopens Appendix A so the Macro can rerun through the loop without overwriting the previous data - PUT APP A TEMPLATE FILE PATH HERE
ChDir "C:\Users\aknox\Desktop\LSS\CD\Macro"
Workbooks.Open Filename:= _
"C:\Users\aknox\Desktop\LSS\CD\Macro\Appendix A.xlsm"
End With
End If
Next ws
End Sub
Edit: Also, it is now including the 3 sheets it should not in the loop. How can I fix this?
1.) "zAward Template - Test - Copy"
2.) Appendix A
I have written a macro that essentially takes the data I need in the template file from a tab and paste what is necessary to populate the appendix, does lookups to data, saves, and then reopens the Appendix A file so the previous information is not over written.
I have a For Each loop that is supposed to go through every tab in the worksheet with the exception of (Award, Appendix A, and Lookup). The problem here is that the macro does what it is supposed to do with the tab it starts on, then when it goes to loop again it repeats the same actions on the same page and I get an error that I can not save the same file name in the same file spot due to the fact that it is not moving on to the next worksheet.
I can not specify which tabs / how many tabs for it to loop through because it will vary depending on which project I am working on.
See below for the VBA I have so far. Do you see why it would not be looping onto the next sheet?
Sub LoopTest6()
'Loop - Appendix A Workbook must also be open for this to run
Dim ws As Worksheet
Windows("zAward Template - Test - Copy.xlsm").Activate
For Each ws In ActiveWorkbook.Worksheets
If (ws.name <> "Award") And (ws.name <> "Appendix A") And (ws.name <> "Lookup") Then
With ws
'Copies data from Award Template Workbook and Paste into Appendix A Workbook
' copypaste9261 Macro
Windows("zAward Template - Test - Copy.xlsm").Activate
Range("A2").Select
Selection.Copy
Windows("Appendix A.xlsm").Activate
Range("E4").Select
ActiveSheet.Paste
Rows("4:4").Select
Selection.EntireRow.Hidden = True
'copypaste9692() copies C:K from award file and paste into template
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
ActiveWindow.SmallScroll Down:=0
'Saves the copied cells in the Appendix A as a New Workbook with the Name being a Cell Value (E4)
Dim name As String
carrier = Range("E4").Value
name = Range("E3").Value
appa = Range("E1").Value
ActiveWorkbook.SaveAs Filename:="U:" & carrier & name & appa & ".xlsm", FileFormat:=52
ChDir "U:\Macro"
'Reopens Appendix A so the Macro can rerun through the loop without overwriting the previous data - PUT APP A TEMPLATE FILE PATH HERE
ChDir "C:\Users\aknox\Desktop\LSS\CD\Macro"
Workbooks.Open Filename:= _
"C:\Users\aknox\Desktop\LSS\CD\Macro\Appendix A.xlsm"
End With
End If
Next ws
End Sub
Edit: Also, it is now including the 3 sheets it should not in the loop. How can I fix this?
Last edited by a moderator: