Link to home
Start Free TrialLog in
Avatar of Usha Shankar
Usha ShankarFlag for United States of America

asked on

Need macro/vba code to consolidate data from multiple files into one

I have created a dashboard for which the input is the attached - XYZ LLC. A team member would put in all the information in this file which creates a dashborad. This done for all the events or projects that are handled by the team. Another example is the attachment ABC Firm. I have not really attached the dashboard as my request here is just with the input files. 


The team also has a planning report - attachment named Event Planning. This file is a replica of the input files - XYC LLC and ABC Firm from column A through Column AJ. Except this file consolidates information of all the events handled by the team through out the year. The team right now has to update same information in two places. One is in the input file for the dashboard and second place is the event planning file.


I want to avoid updating same information in two places. So I would like to create a macro which can take information from all the input files and bring that into event planning file. 


Somethings to keep in mind. The input file is created at different times of the year/month. The status and the date would be updated once in a while. All the input files will be saved in a shared drive. Depending on the requirement to create the macro, the files can be saved with a specific name or in specific folder. 


But I just want to able to bring all data from A3 to AJ3 from all input files to the Event Planning file and be able to refresh to update the status or include data from newly created input file. The data shouldn't be duplicated when I refresh or rerun the macro. There will always be one line/row updated in the input file. Any updated after column AJ in the input file can be ignored. Jut FYI, column AM and AS in input file will have multiple lines/rows of data.


Any guidance on how to get this done.


ABC Firm.xlsx

Event Planning.xlsx

XYZ LLC.xlsx

Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Hi Usha,

in the attached file I've made a new sheet named "Start" where you can enter the start folder. This is the folder which needs to contain all other folders with the single XLSX files (limited to XLSX in the current VBA code).

All files found in these folders will be used to insert their values into the Event-Planning Excel file in "Sheet1".
From all found Excel files a sheet with name "Sheet1" is used for import.

Instead of copying the values into the Event-Planning file I linked all the values (1 row, as you said) from A3 to AJ3 of each file into the main file. Excel is now showing all values from the other files and you can easily refresh the data without relinking them again. Use the "Start" sheet with the "Start" button, whenever you have new files not currently in the list or whenever you want to move the files/folders to another location.

Event-Planning.xlsm

The links don't use the formatting of the source files so it is needed that you set the columns in your main file to a format which fits to your needs, i.e. a date format or number format.

If you see a lot "0" and "00-01-1900" that means they are empty in the source file. To avoid displaying that, go to Excel options under "Advanced" - "Display options" and uncheck the option "Show a zero in cells that have zero value".

Cheers,

Christian

Avatar of Usha Shankar

ASKER

I tried to create sample files and saved all of them in one folder and then click on start from the Start sheet. But I see the below error.

I have another question related to the way the data is updated. I have 3 files now that would be imported to the Event planning file. Lets say there are updates made to the 3 files and if I run the macro would it update or refresh the data without duplicating it. We would have status and dates changed as the project moves along. So it would be ideal to refresh or update the existing files and add data from the new files whenever the macro is run. The Firm name/column A is always unique, if you are looking for some unique or ID to avoid duplication.

User generated image
I am not sure if I am listing the folder path correctly. If the files are saved in a shared drive, how should give the path. for example this is what I have given.

Z:\\ams.test.net\root\shared\LA\Imsdata\Test Folder\Dashboard\Individual

I am also kind off thinking, some ppl might have mapped this path with T drive. So how do we work with this.
Hi Usha,

as you can see in the marked row, it is searching for subfolders inside of the base folder.

So, for example, if you have a base folder "C:\MyFolder" then your files should be in "C:\MyFolder\Folder1" and more, like "C:\MyFolder\Folder2" and so on. In these subfolders your files to be linked should be placed, so you can give the different users access to their own folder containing one or more XLSX files.

You can also place them in one folder of course, but need to be a subfolder in the base folder (with the current code). In your example, "Z:\ams.test.net\root\shared\LA\Imsdata\Test Folder\Dashboard" would be the base folder and "Individual" would be automatically searched for XLSX files, but also all other subfolders in "Dashboard". So depending on how you want to do it, simply create at least one subfolder in "Individual" and place one or more XLSX files in there, then the subfolder would be like in your example.
(You have "Z:\\" in the path, should be "Z:\" if you use a drive letter.)

The data is linked using the base folder so you need to use the same drive letter mapping for all who wants to use the EventPlanning file. Otherwise it should also work with using an UNC path which should be always the same for all users. Something like "\\ams.test.net\root..." and so on. Here the double backslash is correct.

The macro is neither updating nor refreshing any data in the main file.
It just create cell links, so i.e. cell "A3" is linked to the external file "ABC Firm" in "Sheet1" the cell "A3". And so on with all the other cells (always only 1 row of them, if you need to use more than 1 row it must be changed).

So after that Excel automatically updates the contents of all cells using the links in the different cells which are pointing to the other files, getting the values from there and showing it in the main file. Excel refreshes usually all values automatically when you open the file but you can also use Refresh in "Data" to do it manually at any time.

So that's what I meant above: The macro is only needed to create the links once. You only need to run it again if you have completely new files which you want to add to the list. If existing files like "ABC Firm" are only changed by the owner of the file, i.e. changing a date or a value, the macro don't need to run again, just open the main file and you see the changes of "ABC Firm" directly. So theoretically you could even delete the macro from the main file if you do not plan to add more single files in the subfolders, it would still work.

The macro searches for all XLSX files in the specified folders and create a 1:1 link from cell A3 to AJ3 of the found XLSX file (of course independent of the XLSX file, if you add a file containing cook recipes in a list, then also their cells A3 to AJ3 would be linked to the main file showing then maybe the recipe components of brownies).

The advantage here is that you don't need to check for any column header or cell formatting, always the data cells of A3 to AJ3 are linked to the main file. One row in the main file is the one row of one of the found XLSX files.

Of course that also means that the data must be equal in all used single files and the main file, means, if your first column is "Project name" then also in all files the first column needs to be "Project name", otherwise the data in the column would be different. The macro doesn't check that. (That's the reason you would see the brownie recipe instead... :) ).

So, to say it clearly again:
Use the macro to assemble the main file one time initially to get the links to all files. After that, use it only if you add a new file which was never before in the main file (but it makes no difference if you use it each time again, it's only not necessary).
The main file then contains links which shows the data of the single files, the values are not physically in the main file and they get updated by Excel itself or click on Refresh, but not by the macro.

Cheers,

Christian
Its perfect the way you have linked. Works very well. However I am not able to go through with the error.

I created sub folder withing the Individual and the path look likes this now

\\ams.test.net\root\shared\LA\Imsdata\Test Folder\Dashboard\Individual\ - this folder has 3 folders named and each of them have one file.

The folders actually start from Shared. This is the main shared folder. From here LA is the subfolder for the region. Within LA Imsdata is another folder> then test folder(actual name is different)>Dashboard>Individual>Test1, Test2, Test3.

I am still seeing the same error. I updated the path to UNC path
And a follow up a question. Should the sub folders have only one file? In my example - folder named Individual had 3 sub folders. Should the 3 sub folders have only one file?

Is it possible to make the Individual as the folder and the files in this folder should be linked to the event planning or the main file.
Hi Usha,

sorry, my fault, had not tested it with UNC pathes.

I've corrected the code, now it should work better.
Please keep in mind, when you enter an UNC path Excel automatically make a hyperlink out of it (at least at my Excel 2013) so to just have the path as text, right click on the entered UNC path cell and use "remove hyperlink".

The sub folders can have one or more XLSX files, that doesn't matter. So you could for example add one file for each year into the subfolders or one file for each region in region folders or whatever you like. Each file will produce one row in the main file.

Yes, could also be the Individual folder and all files placed there (except the main file itself). But as the code searches for all subfolders in the base folder you should have no other folder in "\\ams.test.net\root\shared\LA\Imsdata\Test Folder\Dashboard" or they would also be added if they contain XLSX files.
If you want to use the "Individual" folder I would recommend to place the main file here and add one additional folder, i.e. "Single Files" (or whatever you like) in it containing all the files to be linked.

Event-Planning.xlsm

This is now tested with UNC and worked fine.

Cheers,

Christian
I am sorry, I don't where I am going worng. I still see the same error. Am I not listing it right? Its a shared drive path. This is exactly what I given in the path.


\\ams.test.net\root\shared\LA\Imsdata\Test Folder\Dashboard\Individual

I event tried the below. But not working

\\ams.test.net\root\shared\LA\Imsdata\Test Folder\Dashboard


Capture1.PNG
Capture.PNG
I tried both the ways like you suggested. I saved the main file in Dashboard and saved the input files in indivdual folder. I also saved the mian file in the Individual folder and saved input files in a subfolder withing Individual folder. I am seeing same error for both.

I also have another question. In the sheet 1 once all the information is updated, as time goes the status of the projects will be updated. Once the status is put on hold or terminated, I am going to move them to another sheet. Would the link continue to work the rows are moved to another sheet? In any case if such projects restart, I will be moving it back to the sheet 1. Will the link continue to work once the row is moved back to the sheet 1?
Hi Usha,

when the yellow error line is shown, can you please go to the Immediate window (if not visible, enable it in the "View" menu of VBA) and enter that:

?ThisWorkbook.Worksheets("Start").Range("$C$7")

Open in new window

When you press enter after that you should see the path which the code tries to open.
To test if the path works, copy it to Windows Explorer to test if it works.
Please show what you got in the VBA Immediate window here.
Please keep in mind what I said above: If the UNC path is shown in blue and underlined, Excel made a hyperlink out of it which internally adds a "file://"string before the path. If it is a hyperlink, right click on the cell and choose "remove hyperlink", so the path is a text only after that.

When you copy/paste the linked cells, make sure that you don't paste the links into the other sheets, otherwise you will get the current values of the external workbooks, same as with your "Sheet1". So if you want to save the values as archive, copy the cells and then use "paste values" only (in the paste icons, it is the icon with "123").

The link in "Sheet1" will still continue to show the values of the external workbook. So if you reset the values in "ABC" you would see that also in the main workbook (after refreshing/reloading).
If you have pasted the values only of course that is no link anymore and if you copy/paste it back it would only show the archived values.

So I would recommend to copy/paste the values only as archive to another sheet, but don't copy/paste it back. Whenever you run the macro again, all external workbooks are collected and linked again beginning with row 3, so everything you changed manually here would be deleted.

The simpler way is: In the subfolder where the "ABC" file is, create a new folder, i.e. "Archive" or whatever you like and move (not copy) the "ABC" file into the "Archive" folder.
The script only searches for files in folders of the base folder only, not in subfolders of these folders. So the "Archive" folder would be ignored. The macro would not find the "ABC" file anymore and would not insert links for it anymore so the row would be removed in the main file.
(Please keep in mind that the previous contents of the main sheet will not be deleted by the macro, so if you have one or more rows less than before you would see the old contents in the main sheet at the end. To avoid that, simply delete all rows below the header (starting with row 3) in the main sheet before running the macro again, that makes sure that you always get the right information displayed.)

With this method you don't need to archive the values in the main sheet, as you have the "ABC" file now in the "Archive" folder. If you want to start the project again, just copy it back to the previous folder and change it's values like you want. On that way you have a copy of the old values in the "Archive" folder and the new values in the normal folder. If you need to do it again, move the "ABC" file again to the "Archive" folder. Explorer would say, this file already exists, so let it rename it or change the name in the "Archive" folder manually, maybe with a date string at the end or whatever you like.

Of course you can also save the row in the main sheet to an extra sheet in the main file, but without moving/deleting the "ABC" file from the folder it would be inserted again when you run the macro again.

("ABC" file stands for all the single external files, but I think you have already got that. Only want to mention it to avoid confusion... :) )

Cheers,

Christian
I opened the immediate window and hit enter after the yellow line in the code, nothing appears in the immediate window.

I understand how the archival works with this set up. But at any point in time if the management wants a list of projects that are in archive, how do achieve that?

I was thinking I can create two more tabs one for closed and one for terminated. And move the projects to respective sheets and the individual input files will be moved to respective sub folders within Individual folder. In case a terminated project is restarted the file can be moved from archive to individual, and delete the line from the terminated sheet so when the macro is run it recreates that line and the link.

But I guess there is a bit of maintenance required here. If the project line is moved from sheet1 to archive but if the file is not moved to archive then when the macro is run the line is again created in sheet 1. So I am just thinking out loud to avoid this mishap. Its very normal for people to forget to move the files into archival.
Hi Usha,

if you hit enter after the yellow line in the code you insert a new line only... ;)

You should wait until the error comes up (so the yellow line appears) and then copy and paste the code line as mentioned above (beginning with "?") into the Immediate window and then press enter there. Then it shows you the value (=the path) which VBA has read from the cell.

Archive: Yes, that's of course true, with the method described above you would see the archived projects only in the different "Archive" folder. You could of course write another macro to collect the information of all "Archive" folders (if all subfolders have the same name "Archive") - or, even simpler, add an additional column into main file and single files with indicating that it is an archived project (if I remember right there is already a "project status" which you also could use). In this case it is just a simple process to add an auto filter to the main file and filter out all projects with an "archived" status or with an additional "Archive" flag column.
With this method you would also not need to create archive files, except you want to save a specific value state.
Just keep the data in the main file and filter out what you currently don't want to see - very much easier.

Of course you can also create archive sheets in the main file - you could also create a macro which do all that automatically (using the current row in the main sheet to move it to an archive sheet and also move the corresponding file into an archive folder), but you end with a lot of programming where simply filtering the unwanted rows from the main sheet is a lot easier.

In general (as it's normal for me to say that as being mainly a database programmer) I would use a database for that purpose where managing such lists are very much easier, where several people can use either their own data or also the main list, where data exists only once, where searching and filtering is very much easier, where more than one person can access the data at the same time, where a manager can get any kind of report like "how many projects are currently waiting for anything?" or "how many projects are currently running?" and so on.
Good planned this data can help anyone in the company, because for example you don't need to enter the same project data in different tools, you can simply use project data defined once in any other resource like an Excel output list, a PDF file, a Word letter, another database, a website and so on.
These kind of Excel "solutions" like the current one are more or less isle solutions for one specific purpose and if you get a new job to do you create another isle without using the already entered data in another solution (and they are never be actual if not all such files get updated).
But I mentioned that only to give you another view on the maintenance of data... :)

Cheers,

Christian
I am sorry :) I did paste the line and I got the below in the immediate window

\\ams.test.net\root\Shared\LA\Imsdata\Test Folder\Dashboard

when I ran this in the internet explorer I see an error - File not found, It may have been moved, edited or deleted.

I must say you are great at explaining things. I really appreciate you taking time to explan. Thank you very much. I like the filtering idea. There is a project status column in the sheet. If the status is changed to Termintated, I want this to be filtered out while the macro is updating or refreshing the data. If we do this, I assume we don't have to move the files to the archive. Macro will read the status and stop refreshing the data where the status is 'Terminated'. Is my understanding right?

If yes, how can I change the code to include these changes
Hi Usha,

we are getting closer... :)

So, if you now copy and paste this path from the Immediate window to the path row in Windows Explorer (not Internet Explorer) you should get the contents of the folder "Dashboard" listed if everything is fine. Inside of "Dashboard" there must be at least one subfolder which is then used for the single files.

Thanks for your kind words, I'll try to do my best to get your project up and running..:)

That sounds good, filtering would help you a lot with getting the needed information ad hoc without any programming or archive maintenance.
No, moving to an archive folder would not be needed, because if you start a project again you would only need to change the project status in the single file accordingly and refresh the main sheet.
You would only need to copy the file to an archive folder if you want to see the status before restarting it.

The macro don't need to be changed for that.
  • if the project is running the user changes the "ABC" file each time and you get the current values by refreshing/reloading the main sheet.
  • if the project stops you still get the data from the "ABC" file on the same way - but from then on the data will not be changed anymore as it is stopped. So it doesn't matter if it is refreshed in the main sheet - the data doesn't change
  • if the project restarts, the user begin to enter new data into the "ABC" file and the main sheet now shows the new values automatically.

Means: As long as your links exists in the main sheet you get the current state of all used files. The macro exists only to create the links between the main sheet and all the single sheets. You only need it one time: After you inserted all single files into the subfolder (or separated in several folders, like you want) and from then on you could even delete the macro, not needed anymore.
The only point in time when you need the macro again would be, if you add a new single file, a new project, which was not in the main sheet before. Or when you move all the files and folders to a different path to restore all the links. Or if you delete a single file.

In the main sheet you would just add a filter and filter in or out whatever you want to see (and not only by project status, you could i.e. list all projects beginning with "A" only - whatever is in the data of the main sheet).
And the best is, as normally most people nowadays knows how to use the auto filters in Excel you can also give it to the manager interested in data and then let him choose the filters and filter values like he want.

Cheers,

Christian
I have to apologize for my ignorance. I had skipped a whole
Folder and thats why I was seeing the error. But I was able to figure it once I put the path in windows explorer. I am sorry for this.

I ran the macro it works fantastically. Just a few things I noticed:

1. To test I updated data in individual files and refreshed the main file and data didn’t get refresh. I even tried saving and closing the individual file. I ran the macro again and it refreshed. If this cannot be resolved its fine. But I just wanted to know if I was doing wrong.

2. When I select a cell in the main file, I van see the link/path to the individual files. Is there a way to get rid of this. The reason being
a) I have data validation in few columns. If I have data validation in the individual files will it carry over to the main file? How will the link show then?
b) I am going to have formulas in few columns in the main file. This information will always be blank in the individual files.  The formulas is to mostly take sum of accts opened etc. how can I add formula if I see the link/path in the formula bar

3. In the main file I am converting the range to a table. I didn’t test this yet but will it be a problem if the data is in table format
Hi Usha,

no problem, that's why we debug together to find the problem. So good that it works now - congrats!

Your questions:
1.: There are a lot different ways and settings you can use to refresh the data - that would be too long to describe here, but Microsoft has a good explanation page showing you all you need to know to update the links:
https://support.microsoft.com/en-us/office/control-when-external-references-links-are-updated-21e995b5-bab1-4328-8ab3-dd357fe0e653

2.: A link simply looks into the external file, gets the value from there in any way it was entered or calculated and then show the resulting value in the cell of the link formula. Validation would be done in the single files as these are entered by the user there and this user would need the validation. The main file should only be a collector where data is never changed manually (especially because it would be overwritten if you run the macro again). So you need no validation in the main file.
The link  is a formula like any other formula in Excel. You can of course copy the whole main sheet and insert it to another sheet using "Paste values". In that case you separate the formula from the values and these pure values would never be refreshed - and you can change the values like you want.

You can of course add any own formulas to the main sheet. You only need to make sure that the formula is not in the area between A and AJ because the macro links all the cells in this range to the main sheet and would overwrite any own formula. But you can go into the macro and define for yourself how many columns are linked. There is a comment in the code after "36" which is the number of the column "AJ". So if you change that i.e. to "35" then you would link only A to AI. Or if you change the value "1" in the for/next loop to i.e. 3 you would link from C to AI (For/next would be "3 to 35"). So you can adjust the area to be link like you want.
It's also possible to only link specific cells (don't need to be a continous range), in that case the macro needs to have more changes. You would then need i.e. an array or a collection containing the numbers of the columns you want to link and then the for/next loop would loop through the elements or the array/collection to link only the columns contained in the array/collection. After that you could enter your formula in the columns you don't link and they would never be overwritten. All is possible...:)

3.: In opposite to separated single cells a table is handled like a database table. So at least all datatypes of all values of one column would need to be the same in all single files. I have not tested it, but theoretically it should also work with link formulas. But sometimes Excel handles that differently and tries to automatically copy a formula into all rows of the column where you enter it - maybe that would destroy the links to different files.
But usually you would not need to reformat that as table (not in this case).

Cheers,

Christian
I asked my questions but realized  later that if I dont need to have data validation in the main file.  So that can be managed. My only concern is to use the formulas. To avoid seeing link when you click on a cell I was thinking of copying and pasting using values but it might copy the formulas as well and paste it over with values. Copy paste would also remove the link I believe
Hi Usha,

you can copy/paste the main sheet to another sheet and using paste you have 2 possibilities: pasting like the original, then you copy the links and get the same as in the main sheet or pasting values, then you get the values only which are not linked to anything anymore and therefore never be refreshed (you would need to do the same again if you want to have the values only).

Cheers,

Christian
Perfect! I just tested and if I copy and paste values it helps. I need another help to complete this project.

I am copying the data from sheet1 and pasting in sheet named 'Active Transitions'.
I am converting the range here to table. I am using forumulas in few columns to get the total and I am using column headers in formula instead of the cell reference.

For eg:

Column AO has this formula
=[@['# Expected Bank Accounts]]+[@['# Expected Brokerage Accounts]]

I am thinking if I add the code to copy and paste the data from sheet1 to Active Transitions and then input/add these formulas in column AO and others. But I don't know the code to add the formulas. Would you be able to help. These can either be added in the same macro or could create a new one and call it at the end of this macro (macro to link and update).

If you can give the code to add formula for column AO, I will use the same to add formulas for other columsn that require.
ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Christian,

This looks perfect. Except I need to add formulas for existing column and not a new column. I made some changes and range has changed now. So this new macro to update the range and the sheet name is good. If we add more columns its easier to change it here than in the macro.

The columns where I need to add the formulas are as follows

Column AN  =[@[Total Bank Funded Amount]]+[@[Total Brokerage Funded Amount]]
Column AO  =[@[Total Funded Amount]]/[@[90 Day SSO]]
Column AS =[@['# Expected Bank Accounts]]+[@['# Expected Brokerage Accounts]]
Column AV =[@['# Opened Bank Accounts]]+[@['# Opened Brokerage Accounts]]
Column AW =[@[Total '# Opened Accounts]]/[@['# Total Expected Accounts]]

I am changing the table style to None and hope it won't be an issue. With this you said the sheet names on both individual input file and main file should be the same right?

I also see that it creating another sheet to paste the data. This main file will an ongoing file for the whole year. So can this pasted to an existing sheet named - 'Active Transitions'

I am sorry for so many additions and changes.
I am also changing the file type from xlsx to xlsm.
Hi Usha,

it's time now that you start to learn how you can change that on your own. You will always need to change something in future and if you don't want to wait for others like me your project cannot go on if you are not able to do it by yourself.

In case of a table in Excel you need to have a continous area, and as your data area can change later on you should not add formulas outside at a specific column as they maybe would be overwritten, so simply add them ad the end of the data table and it will always work.

Now how to add more formulas: Just open the VBA editor with ALT-F11 and then open the code window of the "Start" sheet (if not visible, open the "Project Explorer" window in the "View" menu).

Here you find the code of the lower button of the Start sheet in the sub "cmdCopyValues_Click".

At the end of the sub you will find that:
            With .ListColumns.Add
                .DataBodyRange.FormulaR1C1 = "=[@[Expected Bank Accounts]]+[@[Expected Brokerage Accounts]]"
            End With

Open in new window


You can change the 3 rows to one:
            .ListColumns.Add.DataBodyRange.FormulaR1C1 = "=[@[Expected Bank Accounts]]+[@[Expected Brokerage Accounts]]"

Open in new window


Using that you can simply add more formulas by copying this row and pasting it under the existing one.
Then the only thing you need to do is, entering the next formula between the quotes, in the same way as yo see in the existing one.
(Remove the '# from your formulas as inside of a table you don't need that.)

That's all! So with that you are able to extend more columns with more formulas in future.
They appear in the order of rows you add to the code.

The next step in advanced working with Excel waits for you now... :)
Hi Christian, I am sorry for the trouble. I do know to edit the codes. I sure don't write them but I google or youtube and use the codes from there. But your code was different and frankly I didn't know how to edit. I did not run this macro when I requested for more changes. I read the code and understood that its adding new column to add the formulas and I also understood that its creating a new page to paste the copied data.

However, I ran the updated macro now and realized that its not linking the data appropriately. The only change I made in the code was to replace file format from xlsx to xlsm.

In the start sheet - I updated the same path, I updated the sheet name as Sheet1 - which is the same for all individual files and the mail file.
I also updated the range i.e, A3 to BB3 (This is the new range).

When I run the macro its linking the first 3 columns from the last individual file. For example if the individual files are Test1, Test2 and Test3, its linking the row A,B and C from Test3 into the main file. I am not sure why. there is no entry from the other 2 files.

I have also updated the below line in the cmdCopyValues_Click code.

 Set objSheetTarget = ThisWorkbook.Worksheets("Active Transitions")

Your code said to add a new sheet. And this is working fine.

Lastly, is it not possible to add formulas in between the columns? or it is not a good idea to add and hence you are suggesting to add the column at the end.

When I ran the cmdCopyValues_Click code I got the attached error to add a column

I apologize as this sis taking longer than you could had expected.
Capture4.PNG
Capture3.PNG
I recreated the input files and is working now. I am not sure what was wrong. I only have the add column and formula error now. I removed that line to test and its working fine without that line.

The very last is to add formulas into respective columns. I removed the table in the sheet where the data is pastedand converted it to normal range. If it helps, now can the formulas be added to the columns?

Like AC=AA+AB
Hi Usha,

glad to see that you were able to fix the error!

ListObjects (= range transformed to a table) are a little bit tricky. You cannot create a table at a position where alread a table exists. That's why I simply add a new sheet with a new ListObject so they never get in their way. Otherwise you would need to check for existing ListObjects and delete them first before inserting a new one.

Many macros you see out in the Internet are more or less the result of the Excel macro recorder (you know that one? You can start recording a macro, then do anything in Excel which you want to automate and stop the recording - the macro would contain everything you have done in between and repeats exactly the same). The result of such "code" is often that it contains many unnecessary code which slows down the result. VBA is a full programming language which can do much more than that (and that's why VBA programmers don't like the word "macro" as it is a downgrading name for VBA). The macro recorder produces macros as it is a 1:1 reproduction of what you have done manually, like a voice recorder which repeats what you have recorded. But real programming needs to know how to use different objects, reading helpfiles to find out which objects can be used at which way and so on. The macro recorder can be a great help to avoid searching for the right properties and methods of the different objects, and for that purpose I also use it. But then take the info and start programming - in the result you see that it works really fast and efficiently.

So back to your questions: Of course it is possible to insert formulas in between, especially but not exclusively when you don't reformat the area as table. The solution I wrote already above (look for the array/collection information above) and as you can see, that's of course a lot more work than just copying/pasting an area which is continous.
Especially if you think about that such tables changes from time to time (as you just have here with now range to BB) and you would need to know where the column is where the new formula should be inserted in between. So if you have a column header i.e. "My Sum" at column N and your new tables have new columns inserted in columns C, G and H your "My Sum" column moved 3 columns to the right - that means, your code also need to know that. So you can either use cells like I've done in the Start sheet to save all positions and all formulas or constants in the code for that (like in "modFSO" at the beginning). And not to forget that all further formulas (their address) at any righter position than this also needs to be changed.
In either method you would now need to change the column address so that the code knows where to insert it now. Or the code needs to scan the header row and search for the name "My Sum" to find out the column where to insert it now.

So you see, that all is of course possible, but needs a lot more programming/testing (and I have even not inserted error handling code to avoid users get into the VBA editor in case of errors like you already saw).

Inserting the sums at the end is easier and that was possible in a relative short amount of time. I have unfortunately not the time to program a complete allround application for you - and that's also not the purpose of a forum in general.

I hope you understand that and that I don't mean any harm saying that, so, I have no trouble and everything is fine...I simply run out of time as there are a lot other questions (and not only here) waiting to be answered... :)

Cheers,

Christian
No worries Christian. I’ll
Figure a way to out. Thank you for your assistance on this.