Añadir una nueva hoja de cálculo a un libro utilizando VBA.

El código siguiente muestra cómo la nueva hoja obtiene su valor de una celda de una hoja de cálculo ya presente.

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

Crear una nueva hoja y copiar los datos

Por último, lo más probable es que añada algunos datos a la nueva hoja de cálculo. A continuación asumo que los datos provienen de otra fuente de datos abierta en primer lugar.

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