JEff said:
other files into the loop and have it run. "wrkBk" is my variable while
the
Worksheet name stays the same. How do I loop through the list of filenames
and set "WrkBk"
HEre is the loop
For R = 7 To 46
Cells(R, CurMth) = Workbooks(WrkBk).Worksheets("T&O Project
Plan").Cells(R, CurMth)
Next R
Hi Jeff,
Assumed: the intention is to copy rows 7 to 46 in the CurMth column, on
Worksheets("T&O Project Plan") of the workbook holding the code, to
the corresponding range on identicallly named worksheets in each of seven
closed workbooks,
Providing that this accords with your requirements, try:
Sub Tester()
Dim Arr As Variant
Dim lb As Long, ub As Long
Dim i As Long
Dim CurMth As Long
Dim wb As Workbook
Dim Rng As Range
Dim MyPath As String
Dim sStr As String
CurMth = 1
sStr = "Sheet1" '"T&O Project Plan"
MyPath = "C:\MyDestinationDirectory" & "\" '<<==== CHANGE
With ThisWorkbook.Sheets(sStr)
Set Rng = .Range(.Cells(7, CurMth), .Cells(46, CurMth))
End With
Arr = Array("Test1.xls", "Test2.xls", "Test3.xls", _
"Test4.xls", "Test5.xls", "Test6.xls""Test7.xls") '<<=== CHANGE
Rng.Copy
lb = LBound(Arr): ub = UBound(Arr)
For i = lb To ub
Set wb = Workbooks.Open(MyPath & Arr(i))
Rng.Copy Destination:=wb.Sheets(sStr).Range(Rng.Address)
wb.Close SaveChanges:=True
Next
End Sub
At the first line marked "'<<=== CHANGE", you will need to change MyPath
to reflect the full path of the directory housing the 7 workbooks.
At the second line marked for change, you need to insert the names of the
7 workbooks.
Until you are happy that everthing is functioning properly, test the code on
copies of the original workbooks!
