Adding worksheet in another workbook

[Tompa70]

Hi,

I have 2 workbooks and I want to add a sheet in the 2nd workbook and name the new sheet from a cell in the 1st workbook

Code after recording:

Sub veckonummer()
Range("R33").Select
Application.CutCopyMode = True
Selection.Copy
Windows("Kopia 2004.xls").Activate
ActiveSheet.Select
Application.CutCopyMode = True
ActiveSheet.Name = "0401"
Range("B1").Select
ActiveWorkbook.Save
End Sub

This row is hardcoded I want it to be as referens so if I change the "0401" it will name it according to the change.
ActiveSheet.Name = "0401"


/Thomas

 

[Juan Pablo González]

Hi, welcome to the board.

Is this what you're looking for ?

Sub SaveOtherBook()
    With Workbooks("Kopia 2004.xls")
        .Sheets(1).Name = Range("R33").Value     'Change the name of the first sheet to the value of cell R33
        .Save    'Save that workbook
    End With
End Sub

 

[Joe4]

How about something like:

Sub veckonummer()

    Dim myName As String
    Range("R33").Select
    myName = Range("R33").Value
    Windows("Kopia 2004.xls").Activate
    ActiveSheet.Name = myName
    Range("B1").Select
    ActiveWorkbook.Save

End Sub

 

[Tompa70]

Thx for the fast answers.

Both examples works fine though they change the name of an existing sheet, I want it to add a new sheet in the 2nd workbook with the value in "R33" wich changes every week. I got around to it finally.

 

[Joe4]

Simply add the line "Sheets.Add":

Sub veckonummer()

    Dim myName As String
    Range("R33").Select
    myName = Range("R33").Value
    Windows("Kopia 2004.xls").Activate
    Sheets.Add
    ActiveSheet.Name = myName
    Range("B1").Select
    ActiveWorkbook.Save

End Sub