Iterate through two columns and follow hyperlink

I have a workbook: Col. C & Col. AG have hyperlinks to other sheets in the same workbook. I want to iterate through col. C to open the hyperlink, copy the sheet Then return to the main sheet to Iterate through col. AG to open the hyperlink, and paste the sheet that was copied, and do that until the end of both columns. Any help with my code. Thanks in advance Here's my code:

Option Explicit
Sub copySheets()
    Dim Sh          As Worksheet
    Dim Rng         As Range
    Dim Cell        As Range
    Dim strAddress  As String
    Dim copyRng     As Range
    Dim SecRng      As Range
    Dim SecCell     As Range
    
    Set Sh = Worksheets("List")
    
    With Sh
        Set Rng = .Range("C2:C" & .Cells(.Rows.Count, "C").row)
        Set SecRng = .Range("AG2:AG" & .Cells(.Rows.Count, "AG").row)
    End With
    For Each Cell In Rng
        If Cell.Hyperlinks.Count > 0 Then
            Cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
            Range("A1:L45").Select
            Set copyRng = Selection
            Selection.copy
        ElseIf Cell.HasFormula And InStr(Cell.Formula, "=HYPERLINK(") > 0 Then
            strAddress = Split(Cell.Formula, Chr(34))(1)
            ThisWorkbook.FollowHyperlink strAddress
            Range("A1:L45").Select
            Set copyRng = Selection
            Selection.copy
        End If
        Application.Goto Reference:=Sheets("List").Range("A1")
        
        For Each SecCell In SecRng
            If SecCell.Hyperlinks.Count > 0 Then
                SecCell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
                Range("P1:AA1").Select
                copyRng.PasteSpecial
            ElseIf SecCell.HasFormula And InStr(SecCell.Formula, "=HYPERLINK(") > 0 Then
                strAddress = Split(SecCell.Formula, Chr(34))(1)
                ThisWorkbook.FollowHyperlink strAddress
                Range("P1:AA1").Select
                copyRng.PasteSpecial
            End If
            Application.Goto Reference:=Sheets("List").Range("A1")
            
        Next SecCell
    Next Cell
End Sub

Solution

Try something like this (if I've understood your plan correctly). Pulled out the "get linked sheet" logic into a separate function.

Sub CopySheets()
    
    Dim wsList As Worksheet, wsCopy As Worksheet, wsPaste As Worksheet, Cell As Range
    
    Set wsList = ThisWorkbook.Worksheets("List")
    
    For Each Cell In wsList.Range("C2:C" & _
               wsList.Cells(wsList.Rows.Count, "C").End(xlUp).row).Cells
        Set wsCopy = LinkedWorksheet(Cell)    'sheet to copy from
        If Not wsCopy Is Nothing Then         'found a sheet?
            Set wsPaste = LinkedWorksheet(Cell.EntireRow.Columns("G")) 'sheet to paste to
            If Not wsPaste Is Nothing Then    'found the destination sheet?
                wsCopy.Range("A1:L45").Copy Destination:=wsPaste.Range("P1:AA1")
            End If
        End If
    Next Cell

End Sub

'Extract destination worksheet from a cell with a link
'  (links are to sheets in the same workbook)
Function LinkedWorksheet(c As Range) As Worksheet
    Dim rv As Range, f
    If c.Hyperlinks.Count > 0 Then
        f = c.Hyperlinks(1).SubAddress
    ElseIf c.HasFormula And InStr(c.Formula, "=HYPERLINK(""") > 0 Then
        f = Replace(c.Formula, "=HYPERLINK(""", "")
        f = Split(f, """")(0) 'extract the `sheet!Cell` address
    End If
    If Len(f) > 0 Then 'any address found
        On Error Resume Next
        Set LinkedWorksheet = Range(f).Parent
        On Error GoTo 0
    End If
End Function