Ajouter une nouvelle adresse feuille de calcul à un classeur à l'aide de VBA.

Le code ci-dessous montre comment la nouvelle feuille obtient sa valeur à partir d'une cellule déjà présente sur feuille de calcul

Dim wb As Workbook
Set wb = ActiveWorkbook
Dim wsWithValue As Worksheet
Dim strSheet As String: strSheet = "Sheet1"
Set wsWithValue = wb.Sheets(Index:=strSheet)
Dim ws As Worksheet
Set ws = wb.Worksheets.Add
Dim strRange As String: strRange = "A1"
Dim rng As Range
Set rng = wsWithValue.Range(Cell1:=strRange)
Dim strName As String: strName = rng.Value
ws.Name = strName

Créer une nouvelle feuille et copier les données

Enfin, vous ajouterez probablement des données dans la nouvelle feuille de calcul. Ci-dessous, je suppose que les données proviennent d'une autre source de données ouverte au préalable.

Dim wbDestination As Workbook
Set wbDestination = ActiveWorkbook
Dim wsDestination As Worksheet
'add the new worksheet
Set wsDestination = wbDestination.Worksheets.Add
Dim strRangeDestination As String: strRangeDestination = "B2"
Dim rngDestination As Range
Set rngDestination = wsDestination.Range(Cell1:=strRangeDestination)

'get acces to the source
Dim wbSource As Workbook
Set wbSource = Workbooks.Open(Filename:="C:\Code VBA\Demo\Data.xlsx")
Dim wsSource As Worksheet
Dim strSheetSource As String: strSheetSource = "Sheet1"
Set wsSource = wbSource.Sheets(Index:=strSheetSource)
Dim strRangeSource As String: strRangeSource = "A1"
Dim rngSource As Range
Set rngSource = wsSource.Range(Cell1:=strRangeSource)
'copy the value
rngDestination.Value = rngSource.Value