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