VBA: Consolidate Selected Worksheet from Multiple Workbooks from OneDrive

[datastudent]

Hi,

I'm new to VBA so I would like to ask some help. I have a folder in OneDrive which has several workbooks. Each workbook has multiple sheets and each of them are different. I want to consolidate each specific sheet in each workbook and put them in a Master File in my computer. But also customize the sheet name when consolidated in the Master file.

This is what I currently have. I don't know if its correct though. Please help!

Master File: Carrier_Rate_Cards.xlsm
Workbook Name	Workbook Sheet Name	New Sheet Name in Master File
Casiguran Rate.xlsx	ITFS	CasRateITFS
Sorsogon Rate.xlsx	Domestic Rates	SorRateDom
Cawit Rate 01.10.25.xlsx	Code Changes	CawitRate
VoxOut National.xlsx	In	VoxOut
VoxDID MCR.xlsx	Ranges	VoxDID

Sub ConsolidateWorkbooksFromOneDriveWithCustomNamesAndWorksheets()
Dim folderPath As String
Dim fileName As String
Dim wbSource As Workbook
Dim wbDest As Workbook
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim lastRowSource As Long
Dim lastColSource As Long
Dim lastRowDest As Long
Dim newSheetName As String
Dim sheetList As Object
Dim selectedSheetName As String
Dim sheetName As Variant


folderPath = "C:\Users\iReply\OneDrive\Test"
Set wbDest = Carrier_Rate_Cards.xlsm

Set sheetNamesDict = CreateObject("Scripting.Dictionary")
sheetNamesDict.Add "Casiguran Rate.xlsx", "ITFS"
sheetNamesDict.Add "Sorsogon Rate.xlsx", "Domestic Rates"
sheetNamesDict.Add "Cawit Rate 01.10.25.xlsx", "Code Changes"
sheetNamesDict.Add "VoxOut National.xlsx", "In"
sheetNamesDict.Add "VoxDID MCR.xlsx", "Ranges"

fileName = Dir(folderPath & "*.xlsx")
Do While fileName <> ""
If sheetNamesDict.Exists(fileName) Then
Set wbSource = Workbooks.Open(folderPath & fileName)
targetSheetName = sheetNamesDict(fileName)