Using For Each with Multiple Ranges

[SimonGeoghegan]

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.

[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

 

[Fluff]

How about

   Set Rng2 = Sheets("List of Names").Range("A1:A57")
   
   For Each r2 In Rng2
      CurrentFileName = r2.Value
      Set CurrentFile = Workbooks.Open(xpathname2 & CurrentFileName & ".xlsm")
      
      PreviousFileName = r2.Offset(, 1).Value
      Set PreviousFile = Workbooks.Open(xpathname & PreviousFileName & ".xlsm")
      
      'Formatting Code Here
   Next r2

 

[SimonGeoghegan]

Thanks Fluff, that worked perfectly - I thought I had to have 2 separate For Each in operation because I was working off two lists. Lesson learnt!

 

[Fluff]

You're welcome & thanks for the feedback.