SimonGeoghegan
Board Regular
- Joined
- Nov 5, 2013
- Messages
- 68
Hi All,
I am working on some code that will open two files, and copy information from one file to the other.
I've got a For Each set up which finds the names of the files, opens them and then formats as I need. The issue I am coming across arises when I move to the next cell down - it only moves down within one of the ranges, and I cant figure out how to get it to move down on both. I suspect its how I've written the For Each command, but any help is greatly appreciated.
When the code moves to 'Next r' it does as I expect, but the r2 above is being ignored, presumably because of how the For Each has been set up.
Thanks,
Simon
I am working on some code that will open two files, and copy information from one file to the other.
I've got a For Each set up which finds the names of the files, opens them and then formats as I need. The issue I am coming across arises when I move to the next cell down - it only moves down within one of the ranges, and I cant figure out how to get it to move down on both. I suspect its how I've written the For Each command, but any help is greatly appreciated.
Code:
[FONT=Verdana]Sub ActionPlanAmends()[/FONT]
[FONT=Verdana] Dim xpathname As String, xpathname2 As String
Dim rng As Range
Dim rng2 As Range
Dim PreviousFileName As String
Dim CurrentFileName As String
Dim PreviousFile As Workbook
Dim CurrentFile As Workbook
xpathname = "C:\Users\sgeoghegan\Desktop\Test\"
xpathname2 = "C:\Users\sgeoghegan\Desktop\Self Assessments\H2\"
Set rng = Sheets("List of Names").Range("B1:B57")
Set rng2 = Sheets("List of Names").Range("A1:A57")
For Each R2 In rng2
CurrentFileName = R2.Value
Set CurrentFile = Workbooks.Open(xpathname2 & CurrentFileName & ".xlsm")
For Each r In rng
PreviousFileName = r.Value
Set PreviousFile = Workbooks.Open(xpathname & PreviousFileName & ".xlsm")
'Formatting Code Here
[/FONT][FONT=Verdana]
[/FONT]
[FONT=Verdana]Next r
Next R2
End Sub[/FONT]
When the code moves to 'Next r' it does as I expect, but the r2 above is being ignored, presumably because of how the For Each has been set up.
Thanks,
Simon
Last edited: