How to a reference to a new sheet in same workbook

[mikkelw]

Hi. Im trying to make a reference to a new sheet in the same document.
The macro is making a new sheet and then i would like to make a reference on the first sheet to e.g. B2 in the new sheet.

This is what I have tried to make. Can anyone help?



Sub createNewSheet()


'Lav et nyt ark der hedder hvad der står i B16
sheet_name_to_create = Ark1.Range("B16").Value




'hvis der er fejl kommer der en tekstboks op med denne fejl
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
MsgBox "Der er allerede en bil med dette navn"
Exit Sub
End If
Next

Sheets.Add after:=Sheets(Sheets.Count)
Sheets(ActiveSheet.Name).Name = sheet_name_to_create

'Indsæt alle info i arket
Range("B3") = "Bil:"
Range("C3") = sheet_name_to_create

Range("B4") = "Reg nummer"
Range("B6") = "Omkostninger"
Range("B7") = "Købt for"
Range("B8") = "Andre omk."
Range("C4") = Ark1.Range("B17").Value

Sheets("Ark1").Select


Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1).Select
Selection = sheet_name_to_create


ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Select


Selection = ActiveWorksheet(sheet_name_to_create).Range("B2")

[Paul_Hossler]

this logic might be simpler, but I'm guessing at some thing

Don't need to .Select an object (worksheet, cell, etc.) if you can just refer to it

Option Explicit


Sub test()
    Dim wsNew As Worksheet
    Dim sheet_name_to_create As String
    
    sheet_name_to_create = Ark1.Range("B16").Value


    'hvis der er fejl kommer der en tekstboks op med denne fejl
    For rep = 1 To (Worksheets.Count)
        If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
            MsgBox "Der er allerede en bil med dette navn"
            Exit Sub
        End If
    Next


    Worksheets.Add after:=Worksheets(Worksheets.Count)
    Set wsNew = ActiveSheet
    
    With wsNew
        .Name = sheet_name_to_create


        'Indsæt alle info i arket
        .Range("B3") = "Bil:"
        .Range("C3") = sheet_name_to_create
        
        .Range("B4") = "Reg nummer"
        .Range("B6") = "Omkostninger"
        .Range("B7") = "Købt for"
        .Range("B8") = "Andre omk."
        .Range("C4") = Ark1.Range("B17").Value
    End With


    With Ark1
        .Cells(2, .Columns.Count).End(xlToLeft).Offset(0, 1).Value = wsNew.Name
    End With
    
End Sub

[mikkelw]

The thing is, i would like to make several small accounting in each sheet. When I press "ny bil" button, there will come a new small accounting in that sheet.
In this sheet there will come a result when it is done, and I need this result on the first page, that is why I need to refer to a cell in the new sheet.

It is easy to make a reference bye myself and write "='Sheet-name'!B5", but I would like VBA to do this for me. If you understand?

[Paul_Hossler]

The thing is, i would like to make several small accounting in each sheet. When I press "ny bil" button, there will come a new small accounting in that sheet.
In this sheet there will come a result when it is done, and I need this result on the first page, that is why I need to refer to a cell in the new sheet.

It is easy to make a reference bye myself and write "='Sheet-name'!B5", but I would like VBA to do this for me. If you understand?

No, but this refers to a cell on the new sheet and puts the value on Ark1, or did you want a formula

With Ark1
       .Cells(2, .Columns.Count).End(xlToLeft).Offset(0, 1).Value = wsNew.range("B5")


       .Cells(2, .Columns.Count).End(xlToLeft).Offset(0, 1).Formula = "=" & wsNew.Name & "!B5"

 End With

[snb]

Sub M_snb()
   with sheets.add(,sheets(sheets.count))
     .name= Ark1.Range("B16").Value
     .Range("B3:C3") = array("Bil:",.name)
     .Range("B4:C4") = array("Reg nummer",Ark1.Range("B17").Value)
     .Range("B6:B8") = application.transpose(array("Omkostninger","Købt for", "Andre omk."))
     Ark1.Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).value=.name
     Ark1.Cells(2, Columns.Count).End(xlToLeft).Offset(1)="='" & .name  & "'!B2"
   end with
End Sub