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/vba/beyond_the_macro_recorder/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