Have a problem with my looping!!

[romij29]

I have 2 columns in a control worksheet that is a refernce to my macro..
First column has 90 files relating to departments. the 2nd col. has 5 of these 90 files.Basically, my code is supposed to look at each of the five entries in column 2 and if it matches to any of the 90 columns, then go on to open that file in col 1 (that it matches to) and then data is pasted from a source wksheet to this file(destination wksheet).
Problem is once the matching is done, my code can't seem to get out of the loop for the next entries in col 2(2 of 5) to go thru the same procedure..??

Code is :


Sub Squares_Macro()
i = 2
intRow = 2
Application.ScreenUpdating = False
strPath = Workbooks("Squares_Control.xls").Sheets("Control").Range("E2")
strPath2 = Workbooks("Squares_Control.xls").Sheets("Control").Range("E3")

Application.StatusBar = "Opening Files"
Workbooks.Open Filename:=strPath & "Region Alpha.xls"

Do Until Workbooks("Squares_Control.xls").Sheets("Control").Range("A" & i) = ""
'assigns a variable to a named range ,this variable
'will refer to the range whenever used in the following code
strTemplate = Workbooks("Squares_Control.xls").Sheets("Control").Range("A" & i)
strCinema = Workbooks("Squares_Control.xls").Sheets("Control").Range("B" & i)
strWkb = Workbooks("Squares_Control.xls").Sheets("Control").Range("I" & intRow)

If strWkb = strCinema Then

Application.StatusBar = "Opening " & strCinema
Workbooks.Open Filename:=strPath2 & strCinema

While strWkb = strCinema

Workbooks("Region Alpha.xls").Sheets("P&L").Activate
Columns("A:A").Select
Selection.Find(What:=strTemplate, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 0).Range("N3:O61").Copy

Workbooks(strWkb).Sheets(1).Activate
Cells(10, 14).Activate
ActiveCell.PasteSpecial (xlPasteValues)

Range("N10:N68").Select

Selection.NumberFormat = "#,##0"

Range("O13").Select
Selection.NumberFormat = "0.0"
Range("O15:O22").Select
Selection.NumberFormat = "0.00"
Range("O25:O72").Select
Selection.NumberFormat = "0.0%"
Range("N12, N22:O22, N32:O32, N38:O38, N46:O46, N53:O53, N58:O58, N63:O63,N68:O68, N72:O72").Select
Range("N72:O72").Activate

Selection.Font.Bold = True
Range("N10:O72").Select
Selection.Interior.ColorIndex = 2

Application.StatusBar = "Saving " & strCinema
Workbooks(strCinema).Save

Workbooks(strCinema).Close


Wend

intRow = intRow + 1

Else
i = i + 1
End If

Loop

If strWkb = "" Then

Application.CutCopyMode = False
Application.StatusBar = "Closing Region Alpha.xls"
Workbooks("Region Alpha.xls").Close

End If

End Sub

Please get back to me on this,Thanks

 

[Johnny42]

Sub Squares_Macro()
i = 2
intRow = 2
Application.ScreenUpdating = False
strPath = Workbooks("Squares_Control.xls").Sheets("Control").Range("E2")
strPath2 = Workbooks("Squares_Control.xls").Sheets("Control").Range("E3")

Application.StatusBar = "Opening Files"
Workbooks.Open Filename:=strPath & "Region Alpha.xls"

Do Until Workbooks("Squares_Control.xls").Sheets("Control").Range("A" & i) = ""
'assigns a variable to a named range ,this variable
'will refer to the range whenever used in the following code
strTemplate = Workbooks("Squares_Control.xls").Sheets("Control").Range("A" & i)
strCinema = Workbooks("Squares_Control.xls").Sheets("Control").Range("B" & i)
strWkb = Workbooks("Squares_Control.xls").Sheets("Control").Range("I" & intRow)

If strWkb = strCinema Then

Application.StatusBar = "Opening " & strCinema
Workbooks.Open Filename:=strPath2 & strCinema

While strWkb = strCinema

Workbooks("Region Alpha.xls").Sheets("P&L").Activate
Columns("A:A").Select
Selection.Find(What:=strTemplate, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 0).Range("N3:O61").Copy

Workbooks(strWkb).Sheets(1).Activate
Cells(10, 14).Activate
ActiveCell.PasteSpecial (xlPasteValues)

Range("N10:N68").Select

Selection.NumberFormat = "#,##0"

Range("O13").Select
Selection.NumberFormat = "0.0"
Range("O15:O22").Select
Selection.NumberFormat = "0.00"
Range("O25:O72").Select
Selection.NumberFormat = "0.0%"
Range("N12, N22:O22, N32:O32, N38:O38, N46:O46, N53:O53, N58:O58, N63:O63,N68:O68, N72:O72").Select
Range("N72:O72").Activate

Selection.Font.Bold = True
Range("N10:O72").Select
Selection.Interior.ColorIndex = 2
Wend ' hope this does it......

Application.StatusBar = "Saving " & strCinema
Workbooks(strCinema).Save

Workbooks(strCinema).Close




intRow = intRow + 1

Else
i = i + 1
End If

Loop

If strWkb = "" Then

Application.CutCopyMode = False
Application.StatusBar = "Closing Region Alpha.xls"
Workbooks("Region Alpha.xls").Close

End If

End Sub