jo15765
11-15-2011, 02:25 PM
I am running the following Array which works great. Problem I have now is that I need to add a pre-assigned unique identifier
to each varBook. For example, Test1 needs to be 123ABC_Test1, Test2 needs to be DOGcatTest2 etc... I know that I can assign ID1 = 123ABC
and ID2 = DOGcat and I know that I would just need to cycle through the pre-assigned id's just like I would the varBooks. Problem is...
I have no clue how to add 2 loops together. How would I code it to cycle BOTH the unique ID's as well as the varBooks, and 1st verify they match up
then make sure that it appends the unique ID to the varbook?
Dim Varbooks
Dim varBook
Dim wb As Excel.Workbook
Varbooks = Array(Test1", "Test2", "Test3")
For Each varBook In Varbooks
Set wb = Workbooks.Open(Filename:="R:\workbooks\Daily\" & varBook)
Run "RefreshOnOpen"
With wb
With Range("I4")
.Formula = "=TODAY() + 1"
Run "PrintToPDF_MultiSheetToOne_RunEarly"
.Formula = "=TODAY()"
ActiveWorkbook.SaveAs Filename:="R:\Workbooks\Completed\" & VBA.Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1) & "_" & VBA.Format(Date + 1, "mmddyyyy") & ".xls"
End With
End With
Next varBook
to each varBook. For example, Test1 needs to be 123ABC_Test1, Test2 needs to be DOGcatTest2 etc... I know that I can assign ID1 = 123ABC
and ID2 = DOGcat and I know that I would just need to cycle through the pre-assigned id's just like I would the varBooks. Problem is...
I have no clue how to add 2 loops together. How would I code it to cycle BOTH the unique ID's as well as the varBooks, and 1st verify they match up
then make sure that it appends the unique ID to the varbook?
Dim Varbooks
Dim varBook
Dim wb As Excel.Workbook
Varbooks = Array(Test1", "Test2", "Test3")
For Each varBook In Varbooks
Set wb = Workbooks.Open(Filename:="R:\workbooks\Daily\" & varBook)
Run "RefreshOnOpen"
With wb
With Range("I4")
.Formula = "=TODAY() + 1"
Run "PrintToPDF_MultiSheetToOne_RunEarly"
.Formula = "=TODAY()"
ActiveWorkbook.SaveAs Filename:="R:\Workbooks\Completed\" & VBA.Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1) & "_" & VBA.Format(Date + 1, "mmddyyyy") & ".xls"
End With
End With
Next varBook