Combining For Each loops in VBA-CodePudding

this is a follow-up to my post: VBA Macro to replace data based on matching ID columns.

So I've now proceeded to use the code with my live environment data which consists of hundred of rows and ten's of columns of data. My issue is that In my 'Learner Spreadsheet' I only need to pull certain columns of data from the the big database.

The code is as follows:

Sub Button1_Click()
    
    Dim OpenFileName As String
    Dim wb As Workbook
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim m, rw As Range
    
    OpenFileName = Application.GetOpenFilename 'Select and Open workbook
    If OpenFileName = "False" Then Exit Sub
    
    Set wb = Workbooks.Open(OpenFileName, ReadOnly:=True)
    Set wsCopy = wb.Worksheets(1) 'for example
    Set wsDest = Workbooks("Learner data Elliot.xlsx").Worksheets(1)
    
    'Learner Details
    
    For Each rw In wsCopy.Range("B2:F" & wsCopy.Cells(Rows.Count, "B").End(xlUp).Row).Rows
        'matching row based on Id ?
        m = Application.Match(rw.Cells(1).Value, wsDest.Columns("A"), 0)
        'if we didn't get a match then we add a new row
        If IsError(m) Then m = wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row 'new row
        rw.Copy wsDest.Cells(m, "A") 'copy row starting from column A
    Next rw
    
    'Programme Details
    
    For Each rw In wsCopy.Range("S2:T" & wsCopy.Cells(Rows.Count, "B").End(xlUp).Row).Rows
        'matching row based on Id ?
        m = Application.Match(rw.Cells(1).Value, wsDest.Columns("A"), 0)
        'if we didn't get a match then we add a new row
        If IsError(m) Then m = wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row 'new row
        rw.Copy wsDest.Cells(m, "I") 'copy row starting from column I
    Next rw
    
    'wb.Close False no save
    MsgBox ("Done")
        
End Sub

The way the code works is that it takes the data from Column B2:F from the main database and then compares the ID's against the Learner Database. If the ID's match it will overwrite the data in B2:F with the newly imported data, and if the ID's don't match it will create a new row in the Learner Database and fill in those 5 columns.

The first For Each loop works perfectly fine. However, now I need to jump ahead in the main database and copy from columns S2:T and put them into column I:J in my Learner Database.

The main cause of the problem in the second For Each loop is that because I am starting at S2 it is not reading the ID's from B2 as it would in the first loop, meaning that it is unable to match the ID's and therefore is creating a bunch of new rows with the data in I:J.

Is there anyway to merge the loops together so that I can display the data from B2:F and S2:T at the same time? I have spent a few hours playing around with different approaches but nothing I seem to try works, so I would greatly appreciate it if anyone could either help me out or at least point me in the right direction.

Thank you!

UPDATE

Based off of the comment from @Apostolos55, I'd like to try and offset "S2" by (0, -17). However, when trying to implement this into my for each loop in my code I have been struggling to get it to work. Would anyone be able to help either demonstrate using my current code above or point me in the right direction as to how I might be able to get the outcome I desire?

For Each rw In wsCopy.Range("S2:T" & wsCopy.Cells(Rows.Count, "B").End(xlUp).Row).Offset(0, -17).Rows
    'matching row based on Id?
    m = Application.Match(rw.Cells(1).Value, wsDest.Columns("A"), 0)
    'if we didn't get a match then we add a new row
    If IsError(m) Then m = wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row 'new row
    rw.Copy wsDest.Cells(m, "I") 'copy row starting from column I
Next rw

I have added Offset(0, -17) to the code as shown above, which is returning a match but it is copying the data from B2 instead of S2.

CodePudding user response：

I managed to come up with a work around for this issue by creating a new worksheet for sorting the columns in the correct order to avoid having to deal with the issue.

Thank you once again to all who commented.