adding new data to existing data table in VBA

Hello people

I have an excel model set up that allows me to data cleanse and process a quantity of month by month data for further analysis, on a worksheet called 'Data Model'. Elsewhere in the same workbook is a worksheet called 'Data Masterfile' that compiles all of the data processed for previous months, which is used for the analysis mentioned earlier. What I want to do is add a button to the 'Data Model' worksheet that will copy the latest data that's been processed and then paste it as values to the bottom of the 'Data Masterfile' sheet. The number of columns to be pasted is the same every month (so in this case the data ranges from column C to column AA), however the number of rows of data can vary from month to month.

How do I do this in VBA? Can anyone help please?

Thanks
Kenny

Hello Bonnister. How do you determine what

the latest data that's been processed

is? Is it the entire 'Data Model' sheet or only parts of it?

Hi there

It's only a part of the sheet, it's set up so that one section accommodates a download of original data and then a neighbouring section processes that data into a finished article, which is then added to the masterfile of data for use in various reports. In this instance the section in question accommodates the range L23:AJ109, and as I detailed above although the column references will remain the same the number of rows will vary dependent on the volume of original data for the given month.

Thanks
Kenny

Try the following code. If it doesn't work for you, perhaps you can post a copy of your file. If we can see the file, it would make it easier to help.

Please Login or Register  to view this content.

Hi again

I've attached a mock-up of the model I'm using, as you can see in the attached file the yellow highlighted section on the 'Data Model' tab is what I want to be able to add to the bottom of the 'Data Masterfile' table. The columns and column headers will be consistent from one month to the next so it's only the data I need to copy, but the number of rows will vary month on month based on the volume of original data to be processed

Hope this helps

Thanks
Kenny

Hi Kenny: Try the attached file. Let me know how it works out.

Hi Mumps1

Works absolutely perfectly, spot on! Thank you very much for your help

Kenny

Hello again

sorry but there is one more glitch and I should have mentioned it firsthand - the data that I'm copying contains formulas but I want to copy and paste the data as values into the 'Data Masterfile' sheet. I've tried adding

Please Login or Register  to view this content.

at various places in your code but can't get it to work. How do I amend the code so it does this please?

Thanks you
Kenny

This should do it.

Please Login or Register  to view this content.

Hi Mumps1

Perfect, does exactly what I need, appreciate your time helping me with this thank you

Kenny

Glad it worked out.