Facing difficulties in developing incremental function (Excel VBA)

[Sky_love]

Hi guys,
I am facing difficulties in developing incremental functions for my excel vba. My current excel macro can't generate incremental number by +1. I will explain in more details in the following.
Firstly, i have a macro file(file named: Still working on it.xlsm) there are one button inside this macro files with assign macro vba the function of this macro which will actually convert a file (file named: File Header.xls) into certain formats in text files(PaymentFile01.txt).
These are the following output in text files
00|MAABBJQQ|AJ_20161206_001||||||||||||||||||||||||||
I need to make it incremental like this whenever it convert to the text files (PaymentFile01.txt). This paymentfile01.txt. Whenever i clicked the button in my excel vba (still working on it.xlsm), the files will be replaced and this AJ_20161206_001 will increment by +1 then it will become
00|MAABBJQQ|AJ_20161206_002||||||||||||||||||||||||||

i do have a sample on how to do this, it's based on Date( Sample function that i need.xlsm),but i can't integrate it into my code in Still working on it.xlsm because it's different situation as it seems to be more complicated.

still working on it.xlsm

[COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] CreatePFHeaderFooter()[/FONT][/COLOR]

[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] x [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas], y [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Long[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] data(1 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]To[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] 29) [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] myfile [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR]
[COLOR=#008000][FONT=Consolas]'file location
[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]   myfile = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"C:\Users\lye.yan.nian\Desktop\File Header.xls"[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    Application.Workbooks.[/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Open[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Filename:=myfile[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    DatFile1Name = ThisWorkbook.Path + [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"\PaymentFile01.txt"[/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Open[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] DatFile1Name [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]Output[/B][/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] #1    [/FONT][/COLOR][COLOR=#008000][FONT=Consolas]'create csv file
[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    x = 2[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]While[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Cells(x, 1).Value <> [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]""[/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Cells(x, 3) = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]""[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Cells(x, 3) = getNewID(Cells(x - 1, 3))[/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]For[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] y = 1 [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]To[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] 28[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]            data(y) = Cells(x, y)[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Next[/B][/FONT][/COLOR]

[COLOR=#151B8D][FONT=Consolas][B]Print[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] #1, Join(data, [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"|"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        x = x + 1[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    Wend[/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]Close[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] #1[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    MsgBox ([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"File PaymentFile01.TXT created"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    ActiveWorkbook.[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Close[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]

[COLOR=#E56717][FONT=Consolas][B]Function[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] getNewID(OldID [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas]) [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] arr() [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas], strDate [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]String[/B][/FONT][/COLOR]
[COLOR=#151B8D][FONT=Consolas][B]Dim[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] d [/FONT][/COLOR][COLOR=#151B8D][FONT=Consolas][B]As[/B][/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Date[/B][/FONT][/COLOR]

[COLOR=#141414][FONT=Consolas]    arr = Split(OldID, [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"_"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    strDate = arr(1)[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    d = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))[/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] d = [/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Date[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        arr(2) = Format(CInt(arr(3)) + 1, [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"000"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Else[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        arr(1) = Format([/FONT][/COLOR][COLOR=#F660AB][FONT=Consolas][B]Date[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas], [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"yyyymmdd"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        arr(2) = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"001"[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]

[COLOR=#141414][FONT=Consolas]    getNewID = Join(arr, [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"_"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Function[/B][/FONT][/COLOR]

Sample function that i need.xlsm

[COLOR=#E56717][FONT=Consolas][B]Private[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Workbook_Open()[/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] [B1] = [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]""[/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [B1] = Format(Now(), [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"dd/mm/yyyy"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]    [B2] = 1[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Else[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR][COLOR=#141414][FONT=Consolas] Trim([B1]) <> Format(Now(), [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"dd/mm/yyyy"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas]) [/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]Then[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [B1] = Format(Now(), [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"dd/mm/yyyy"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [B2] = 1[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]Else[/B][/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [B1] = Format(Now(), [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"dd/mm/yyyy"[/FONT][/COLOR][COLOR=#141414][FONT=Consolas])[/FONT][/COLOR]
[COLOR=#141414][FONT=Consolas]        [B2] = [B2] + 1[/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]
[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#8D38C9][FONT=Consolas][B]If[/B][/FONT][/COLOR]

[COLOR=#8D38C9][FONT=Consolas][B]End[/B][/FONT][/COLOR][COLOR=#E56717][FONT=Consolas][B]Sub[/B][/FONT][/COLOR]

i have project files and the output files are attached in this dropbox link.
https://www.dropbox.com/s/g01hhx45k1fnvfe/The four files.rar?dl=0

Please do help me out, thank you very much.