Define a dictionary with key as
sheetName
and Value as
TableName
, Add all the sheet Name - Table name items to the dictionary
Loop
the items in the dictionary to get the sheet Name and the Table Name and pass it as
Dim dictMapping As Dictionary(Of String, String) = New Dictionary(Of String, String)
dictMapping.Add("Sheet1$", "TableName1")
dictMapping.Add("Sheet2$", "TableName2")
.
.
For Each item In dictMapping
Dim sheetName As String = item.Key
Dim tableName As String = item.Value
Dim sql As String = ("select * from " + sheetName)
. your code
.
.
bulkCopy.DestinationTableName = tableName
Next