VBA excel to word insert footer (Text - Left align) & (Page number - Right align)

Hi All,

Wish you all Happy New Year.

Am having word template that has footer with some text on the left side of the footer and Page X of Y on the right side. Please see below screenshot:
Before.jpg

I have data on excel that needs to be inserted on the footer of word document and hence wrote vba to perform this operation. Below is the entire VBA on excel:

Please Login or Register  to view this content.

Am storing required information in variable called "FooterText" and below line is inserting footer on the left side of the footer:

Please Login or Register  to view this content.

Since I also need page numbers to be displayed, I added below code:

Please Login or Register  to view this content.

But the problem is its inserting page numbers in numerals as 1, 2, 3 etc where as I want it to be inserted as Page 1 of 5, Page 2 of 5 etc.,

Below is the screenshot of footer after running the macro:
After.jpg

I tried to google and searched in forum as well but didn't get much help. I really appreciate any help provided.
Am attaching excel file and word document. Please let me know if any questions.

Regards,
Richa

Hi Experts,

Any help with this please?

Hi Admin,

Can you please move this post under Microsoft Word group instead of Excel programming vba (if any)?

Thanks,
Richa