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

[aknox6]

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?

[Kenneth Hobs]

Welcome to the forum!

My guess is that the ActiveWorkbook or ActiveSheet at some point is not what you think it is.

I have seldom found that Select, Selection, Activate, ActiveWindow, and Windows() are seldom needed. I recommend going beyond the macro recorder's coding method. http://www.tushar-mehta.com/excel/vb...rder/index.htm

In a similar way, I find little use for ChDir.

What I prefer is a more absolute reference scheme. Use "With/End With" to reference one object like a workbook or preferably one worksheet. This can be the source or target workbook.worksheet object.

If you get stuck, post back.

Tip: When posting code, please paste between code tags. Click the # icon on the toolbar to insert the code tags.

[Paul_Hossler]

1. I added CODE tags for you - use the[#] icon and paste your macro between them to format

2. When dealing with 2 or 3 workbooks, I've found I make fewer errors by being very specific

3. When using With ws ../ End With, if you don't 'DOT' the lower object, they default to the active sheet, which might not be the one you think it is

Rows(4) is not the same a .Rows(4) unless the ActiveSheet is the same one as ws


4. Here's an example of some prototype code to show this. Not tested so look out for typos

Option Explicit

Sub LoopTest6()

     'Loop - Appendix A Workbook must also be open for this to run
    Dim ws As Worksheet
    
    Dim wbAppA As Workbook, wbTemplate As Workbook, wbOneWithThisMacro As Workbook
    
    Set wbAppA = Workbooks("Appendix A.xlsx")
    Set wbTemplate = Workbooks("zAward Template - Test - Copy.xlsm")
    Set wbOneWithThisMacro = ThisWorkbook
    
    For Each ws In wbTemplate.Worksheets
        With ws
            If (.name <> "Award") And (.name <> "Appendix A") And (.name <> "Lookup") Then
                 'Copies data from Award Template Workbook and Paste into Appendix A Workbook
                 
                 ' don't need to .Select and .Copy can be on one line
                .Range("A2").Copy wbAppA.Worksheets("SOMETHING").Range("E4")    '   the DOT assumes A2 in ws
                .Rows("4:4").Hidden = True      '   the DOT assumes row 4 in ws
            
            'etc
            'etc
            
            
            End If
        End With
    Next ws
End Sub