FileCopy based on cell reference and loop

[OldsLKJ]

I'm a novice at all of this and have no formal education in coding. I use google and do my best to cobble things together, but I've hit a wall which I will describe below. Thanks in advance for any help you can provide.

I work as an academic advisor in higher education, and for each new student, I must create a folder based on their information (Last_First_emailID) and then copy the curriculum sheet for that student's major into their folder and name it with the student's info (cs_emailID_major). I have cobbled together the code to make the folders, but I'm having trouble figuring out how to copy the curriculum sheet and rename it in the new destination. It needs to go row by row because, while all the original curriculum sheets are in a single folder, the one to copy is based on each student's major, and the folder to copy them to and how to rename them is also dependent on the student info in the spreadsheet. The example below is missing a couple of columns irrelevant to this issue.

A	B	D	G	H	K	L
Last	First	GID	Curriculum Sheet	Folder Name	Source FilePath with Filename	Dest FilePath with Filename
Doe	Jane	jad0001	CSCI	Doe_Jane_jad0001	C:\PATH\cs_GID_CSCI.xlsx	C:\PATH\Doe_Jane_jad0001\cs_jad0001_CSCI.xlsx
Smith	John	jbs0001	MECH	Smith_John_jbs0001	C:\PATH\cs_GID_MECH.xlsx	C:\PATH\Smith_John_jbs0001\cs_jbs0001_MECH.xlsx

Here's the code I use to create the folders after selecting the range in column H:
Folder Creation
Sub MakeFolders()
Dim Rng As Range
Dim maxRows, maxCols, r, c As Integer
Set Rng = Selection
maxRows = Rng.Rows.Count
maxCols = Rng.Columns.Count
For c = 1 To maxCols
r = 1
Do While r <= maxRows
If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
On Error Resume Next
End If
r = r + 1
Loop
Next c
End Sub


I had a module for the file copy, but it was removing the file from the initial destination and renaming it in the new destination - so moving and renaming rather than copying. If two students had the same major, I was running into issues and having to do more work. What I really want is for the code to copy K2 to L2 and then loop to K3 and L3 and so on. Each time I run it, I have a different number of students, but I can edit the code to specify the range each time I run it if necessary. I just can't figure out how to incorporate the cell references and the loop after lots of googling.

 

[OldsLKJ]

Here is the code I've been using to move the files and rename them:

Sub Renamefiles()

    Dim I As Long
    
'Column K has Source Path and Filename
'Column L has Destination Path and Filename

    For I = 2 To Range("K" & Rows.Count).End(xlUp).Row
        Name Range("K" & I).Value As Range("L" & I).Value
    Next I

End Sub

 

[shinigamilight]

Sub Check()
        Dim FSO As Object
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Dim lr, i As Long
        
        lr = Range("K" & Rows.Count).End(xlUp).Row
        
        For i = 2 To lr
                FSO.CopyFile Range("K" & i).Value, Range("L" & i).Value
        Next i

End Sub

 

[OldsLKJ]

Sub Check()
        Dim FSO As Object
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Dim lr, i As Long
       
        lr = Range("K" & Rows.Count).End(xlUp).Row
       
        For i = 2 To lr
                FSO.CopyFile Range("K" & i).Value, Range("L" & i).Value
        Next i

End Sub

Thank you so much, even though words cannot express how grateful I am for your solution.

 

[shinigamilight]

Have a nice day brother, God is great!