Hi Forum,
I think I have a syntax problem. I have workbook where users can generate and name new sheets through a macro. For each new sheet that is generated I add another row to a resume sheet where I need to present relevant data from each sheet in a seperat row. I have named the relevant cells in the sheet which I use as a template. When a user adds a new sheet through the macro a new row is generated which I would like to populate with data from the named cells/ranges of the new sheet in a way that automatically updates the resume whenever one of the other sheets are updated. I need to refer to the specific named range of the sheet on which I do not know the name, only the name variable.
Public Sub NytArk()
Dim Navn As String
Dim lastsheet As Long
Dim bOK As Boolean
Dim LastRow, LastRef, MinimumDato, MaximumDato As Long
Dim i As Integer
Application.EnableEvents = False
lastsheet = Sheets.Count
Sheets("Nyt ark").Visible = True
Navn = InputBox("Skriv navnet på det nye projektark og tryk OK:", "Navngiv projektark", "Indtast projektnavn")
Select Case Navn
Case Is = ""
Sheets("Nyt ark").Visible = False
Exit Sub
Case Else
bOK = True
Sheets("Nyt ark").Select
Sheets("Nyt ark").Copy After:=Sheets(lastsheet)
Sheets("Nyt ark (2)").Select
Sheets("Nyt ark (2)").Name = Navn
Sheets(Navn).Range("B3").Select
Selection.Value = Navn
End Select
Sheets("Nyt ark").Visible = False
Application.ScreenUpdating = False
Sheets("Porteføljeoversigt").Select
i = Range("B4").Value
LastRef = i
Range("B4").Value = LastRef + 1
With Range("B5:AM1000")
Range("B6").Select
If Range("B6").Value = "" Then
Range("B6").Activate
Else
Range("B6").CurrentRegion.Select
ActiveCell.Offset(Selection.Rows.Count, 0).Activate
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Rows(LastRow).AutoFill Rows(LastRow).Resize(2), xlFillDefault
Rows(LastRow + 1).SpecialCells(xlConstants).ClearContents
Rows(LastRow + 2).Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(-1, 1).Activate
ActiveCell.Value = LastRef
End If
End With
Sheets("Porteføljeoversigt").Select
'My problem is here...
Range("D" & LastRow + 1).FormulaR1C1 = Sheets(Navn) & "=Navn!RiskYear"
Range("D" & LastRow + 1).Select
ActiveCell = "=sheets(Navn)Ansvarlig"
Range("E" & LastRow + 1).Select
ActiveCell = "=Navn!VurFremdrift"
Range("F" & LastRow + 1).Select
Selection = "=RiskProject"
Range("G" & LastRow + 1).Select
ActiveCell = "=RiskYear"
' Selection = Sheets(Navn).Names("Projektnavn").RefersToRange
'Sheets(Navn).Range ("B3")
'Range("D" & LastRow + 1).Select
' Selection = Sheets(Navn).Ansvarlig
'Sheets(Navn).Range ("G4")
' Range("F" & LastRow + 1).Select
' Selection = VurFremdrift.Value
'Sheets(Navn).Range ("J4")
' Range("F" & LastRow + 1).Select
' Selection = RiskYear
' Sheets(Navn).Range ("L4")
Application.ScreenUpdating = True
Application.EnableEvents = True
End SubThe task is pretty easy in normal Excel mode but I want to accomplish this proces with VBA.
Regards.
JOR