Hello all,
I have created a macro that creates an overview of several products, based on a cell value in a table with product codes. On the sheets where the overviews are made, I have kept column A free to be able to navigate quickly via a link (defined name ). The first five times the macro runs through successfully, but after that, the name defining step doesn't seem to run. But the rest of the entire macro works perfectly. Also when I press F8 the macro works correctly. Just not when its automated. I do need the whole range to define in connection with printing the overview. I've been looking at this for a long time now but I really can't figure it out, I hope someone can help me.
Sincerely, Justin
Here is the code that is most relevant I guess:
I changed the changed the language in the macro as much I thougt was necessary.
I have created a macro that creates an overview of several products, based on a cell value in a table with product codes. On the sheets where the overviews are made, I have kept column A free to be able to navigate quickly via a link (defined name ). The first five times the macro runs through successfully, but after that, the name defining step doesn't seem to run. But the rest of the entire macro works perfectly. Also when I press F8 the macro works correctly. Just not when its automated. I do need the whole range to define in connection with printing the overview. I've been looking at this for a long time now but I really can't figure it out, I hope someone can help me.
Sincerely, Justin
Here is the code that is most relevant I guess:
VBA Code:
Dim ActivePCode As String
Dim Productsheetsname As String
Dim Location1 As String
Dim Location2 As String
Dim Location2end As String
Dim Location3 As String
Application.ScreenUpdating = False
Worksheets("ProductCodeOverview").Select
Range("C7").Select
Location3 = "C" & ActiveCell.Row
Do Until ActiveCell.Value = ""
Do While ActiveCell.Value = "Navigatie"
ActiveCell.Offset(rowOffset:=1).Activate
Loop
If ActiveCell.Value = "" Then GoTo Einde
Location3 = "C" & ActiveCell.Row
Productsheetsname = Range("O" & ActiveCell.Row).Value
If ActiveCell.Value <> "" Then
ActiveCell.Offset(columnOffset:=-2).Activate
ActiveCode = ActiveCell.Value
ActiveCell.Offset(columnOffset:=2).Activate
If DoesSheetExists(Productsheetsname) Then
Sheets(Productsheetsname).Select
Else
Worksheets("Leeg blad").Copy After:=Worksheets("ProductCodeOverview")
ActiveSheet.Name = Productsheetsname
Range("A1").Value = "Vergeer " & Productsheetsname
End If
Columns("A:A").Hidden = False
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(rowOffset:=1).Activate
Location1 = "B" & ActiveCell.Row
Location2 = ActiveCell.Row
Location2 = Location2end + 36
Sheets("EmptyOverview").Select
Range("A1").Activate
Rows("2:39").Copy
Sheets(Productsheetsname).Select
ActiveSheet.Paste
Range(Location1).Value = ActiveCode
ActiveCell.Value = "Link" & ActiveCode
'It goes wrong here!
ActiveWorkbook.Names.Add Name:="Link" & ActiveCode, RefersToR1C1:="='" & Productsheetsname & "'!R" & Location2 & "C1:R" & Location2end & "C1"
Columns("A:A").Hidden = True
I changed the changed the language in the macro as much I thougt was necessary.