mikkelw
06-11-2020, 06:29 AM
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")
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")