Why will my loop not move through different worksheets (tabs)

aknox6

New Member
Joined
Sep 25, 2017
Messages
6
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?
 
Last edited by a moderator:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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