On Friday, 16 August 2019 17:16:37 UTC+2, GS wrote:
On Thursday, 15 August 2019 21:50:21 UTC+2, GS wrote:
Try again *after* fixing your sheet name:
change "Prod.Ticket'!"
to "'Prod.Ticket'!"
so it *includes* the leading apostrophe!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,
unfortunately, it doesn't work. When I insert a leading apostrophe, VBA
asks me to debug and the code stopped at the first line where it generates
the formula: Range("BT7").Formula = "=" & myPath & wbName &
"'Prod.Ticket'!BT7"

I want to clarify that the code works but when I run a macro, which
increases the product no. in A16 by 1 (e.g. from 10120 to 10121), the code
does not trigger somehow and the values stay the same as for the previous
product no. (10120). But when I put 10121 directly into A16 and hit enter,
the code triggers and changes all values to the ones of 10121. I think
that if I have the product no. increased by 1, through a macro, the code
doesn't recognise the change. It seems to miss the hit on the enter
button.

My next suggestion is going to be to remove the period in your sheetname
because I have found that adding punctuation/space characters to sheetnames
causes coding problems when sheetnames have to be used. I just follow the
same naming rules as for Defined Names and code always works as expected...

Range("BT7").Formula = "=" & myPath & wbName & "ProdTicket!BT7"

..because the apostrophes aren't required!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,
sorry for only replying now. I managed to change my code so it would do what
I wanted. See below the code which I came up with. As I said it might be
awkward but I've never really learned VBA.

I named cell A16 as "CODE". Range A16:W24 is merged. Cell GG1 is just an
empty cell I use to temporarily store the next CODE number to be used.

Range("GG1").Select
ActiveCell.FormulaR1C1 = "=CODE+1"
Range("A16:W24").Select
Selection.UnMerge
Range("GG1").Select
Selection.Copy
Range("A16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _ :=False, Transpose:=False
Range("A16:W24").Select
Selection.Merge
Range("GG1").Select
Selection.ClearContents
Range("A16:W24").Select

After this piece of code there is all my other code wich populates my form
with values of an Excel file which is called <CODE+1.xls (e.g.: 10121.xls)

Ok, that is just to say thank you for your help and in case you are
interested how I am doing.

Cheers

Glad to hear you got it sorted out! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion