Making Code Loop

Hi,

I have this code which works perfectly. However, I want it to loop, but I can't figure out where to adjust the code. The code takes a worksheet from one workbook and replaces a worksheet, with the same name, on another workbook. On my source workbook, I have multiple tabs that I would like this to occur with.

Here's the code:

Sub CopyReplaceWorksheet()
 '--copies activesheet into specified destination workbook
 '  if sheet with same name exists in destination workbook, then
 '    deletes existing sheet and locates copy in same order

 Dim lSheetIndex As Long
 Dim sErrMsg As String
 Dim wkbDestination As Workbook
 Dim wksSource As Worksheet, wksTemp As Worksheet
 
 On Error GoTo ErrProc
 Application.EnableCancelKey = xlErrorHandler
 Application.EnableEvents = False
 
 '--modify to actual workbook name
 Set wkbDestination = Workbooks("my workbook.xlsx")
 
 Set wksSource = ActiveSheet
 '--validate destination workbook is not activeworkbook
 If ActiveWorkbook.name = wkbDestination.name Then
   MsgBox "This macro won't copy Active Sheet in destination workbook."
   GoTo ExitProc
 End If
 
 lSheetIndex = lGetSheetIndex(sSheetName:=wksSource.name, wkb:=wkbDestination)
 
 If lSheetIndex Then
   If lSheetIndex = wkbDestination.Sheets.Count Then
      '--if existing sheet is last in workbook, add temp sheet.
      '  this handles problem of trying deleting only sheet and
      '  simplifies ordering of copied sheet.
      Set wksTemp = wkbDestination.Worksheets.Add( _
         After:=wkbDestination.Sheets(lSheetIndex))
   End If
   
   '--delete existing worksheet with same name
   Application.DisplayAlerts = False
   wkbDestination.Sheets(wksSource.name).Delete
   Application.DisplayAlerts = True
 Else
   '--if no existing sheet, order copy as first sheet
   lSheetIndex = 1
 End If
   
 wksSource.Copy Before:=wkbDestination.Sheets(lSheetIndex)
 
ExitProc:
 On Error Resume Next
 '--delete temp worksheet if exists
 If Not wksTemp Is Nothing Then
   Application.DisplayAlerts = False
   wkbDestination.Sheets(wksTemp.name).Delete
   Application.DisplayAlerts = True
 End If
 
 Application.EnableEvents = True
 If Len(sErrMsg) Then MsgBox sErrMsg
 Exit Sub

ErrProc:
 sErrMsg = Err.Number & ": " & Err.Description
 Resume ExitProc

End Sub

Here's the function for the code:

Function lGetSheetIndex(sSheetName As String, wkb As Workbook) As Long
 '--returns sheet index within workbook if found, else returns 0
 On Error Resume Next
 lGetSheetIndex = wkb.Sheets(sSheetName).Index
End Function

Hi, I think I was overthinking this. I was able to solve it by doing a "for each ws in..." loop. Thanks!