VBA to append multiple columns to last column across multiple worksheets

[PatDools]

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:

month	monthname	year	age range
4	Apr	2019	0-2 yrs

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!

[#2]

@PatDools 

What should the code look up in that lookup table?

[#3]

@Hans Vogelaar 

Hi Hans,

The Lookup Table provides 2 things:

1) The 1st row of the Lookup Table provides the Column Names to be appended to the dataset in the Worksheet
2) The 2nd row of the Lookup Table provides the first row of values that would then be essentially copied down the remaining rows of the dataset on that Worksheet

Please let me know if I've answered your question properly - thank you!

[#4]

@Hans Vogelaar 

Hi Hans - I do hope there is some viable code to help me append columns to the end of my downloaded data sets across multiple Worksheets (within the same Workbook) as noted above. I wanted to follow up to see if any additional clarifications are needed. I do appreciate your help!

[#5]

@PatDools 

I'm sorry, I wasn't notified of your reply.

But I'm afraid I don't even begin to understand what you want.

Could you attach a sample workbook with some explanations?

[#6]

@Hans Vogelaar 

Hi Hans - please find attached a sample Workbook with a series of Worksheets with some data in it (gathered from a publicly-available dataset with no PII info., so no worries on privacy concerns).

I have 3 Worksheets:  'main diagnosis', 'secondary diagnosis', and 'procedures'.  Each Worksheet has a couple columns of data, and the number of columns varies from sheet to sheet.  What I want to do is to add the same four columns to the end of each dataset found on the individual Worksheets based on values from the Lookup Table found on the 'Lookup_Tbl' Worksheet in the attached Workbook.

Lookup Table
month	monthname	year	age range
3	Mar	2022	0-2 yrs

The end result can be seen in the 'expanded' Worksheet tabs, where these 4 columns would be added to the last column of each dataset on each Worksheet, and then the values from the Lookup Table copied down to each row on that Worksheet.  For example, the 'main diagnosis' Worksheet has the original columns provided from the data extract.  Right next to it is a Worksheet called 'main diagnosis exp' which shows the original columns + the 4 columns from the Lookup Table (highlighted in yellow), populated with the data values from the Lookup Table copied down for each record.  The 'expanded' Worksheet tabs are there for illustration purposes only - I'm looking simply to append the 4 columns onto the existing Worksheets, not create additional Worksheets.  Again, the Lookup Table is my workaround the fact that the extract data does not contain these data elements and I thought this would be the easiest way to append these values to the existing dataset.

I hope this makes my request clearer - don't hesitate with any questions you may have.  Thank you - I do appreciate it!

[#7]

@PatDools 

For example: select E2:E9 on the 'main diagnoses' sheet.

Enter the following formula and confirm it by pressing Ctrl+Enter:

=Lookup_Tbl!$A$4:$D$4