VBA to append multiple columns to last column across multiple worksheets

Hello,

I'm looking for VBA code to perform a manual task to reduce risk of copy/paste errors.

I have a Workbook with the following Worksheets:

Starting with the 'main diagnoses' Worksheet and for the remainder of the Worksheets to the right (through 'hospital distribution' Worksheet ), I want to run VBA Code on each Worksheet that appends the following columns to the last column in the dataset on a particular Worksheet (what column the last column occupies is different across the Worksheets):

 1) Column title: month

     Data: the month # of the data extract

 2) Column title: monthname

     Data: the month name of the data extract based on the month # ("mmm" format - ex. Feb)

 3) Column title: year

     Data: the year of the data extract ("yyyy" format - ex. 2019)

 4) Column title: age range

     Data: the age range filter value of the data extract

Unfortunately, the root dataset that I'm appending to in each Worksheet does not have these values that I can extract programmatically from other columns in the dataset .  But I'm thinking I could supply a quick Lookup Table that the VBA code could reference like this:

Ultimately, I'm wondering if there is VBA code that, using this Lookup Table, can do the following:

1)  Append these 4 columns to the end of the recordset in each of the Worksheets in the Workbook (starting with the 'main diagnoses' Worksheet and for the remainder of the Worksheets to the right through 'hospital distribution' Worksheet - 5 total Worksheets, ignoring the 1st 2 Worksheets).

2)  Fill in the values from the Lookup Table for each row of data (down to the last row in the dataset) in that Worksheet and proceeding to do the same each of the remaining 5 Worksheets in the Workbook.

Please let me know if there is code that can help me achieve the above automations.

Thank you!