Forum Discussion
Join and update data
I need to link a specific sheet of the files that you will see below:
I need to link a specific sheet of the files that you will see below
Each File has several sheets, I only need to link a specific sheet from each file, example:
From the File: SOLICITUDES A1, I will need the CUSTOMERS sheet
From the File: SOLICITUDES OC2 , I will need the REQUESTS sheet
From the File: SOLICITUDES M3, I will need the REQUESTSC sheet
CLIENTS, REQUEST AND REQUEST These would be like the source sheets.
my coworkers modify these files every day. I need to create a new file to consolidate the information in a single sheet.
I need to link the CUSTOMERS, REQUESTS, and REQUESTSC sheets and consolidate them into a new file and into one sheet.
I would like this new file to be updated whenever the source files are changed.
If possible if this can be done in a MACRO with Visual Basic that automates the process, that would be great. It is not a Power Query option.
And according to what you asked me
A) Do you want to add the Source data to the end of the Destination data? So they will have both sets of data.
A: I need the source data to be reflected in the new workbook and a single destination sheet.
B) Does the data from the source replace the data from the destination?
Every time the source data is changed, I need it to be seen in the destination sheet.
C) How much data do you have to "move"?
It would be a lot of data, but I don't want to move or modify the source sheet, I'm interested in the destination sheet being modified according to the source sheets.
D) What about the formulas? Do you want their values to hold the formulas?
However, I am only interested in seeing the information.
E) What about cell formatting, column and row sizes, now about conditional formatting, this all has to be copied?
All source sheets keep the same pattern. I just need the header on the target sheet to be created to be only one.
OK when you say "I would like this new file to be updated whenever the source files are changed" that would require some recursive, or human initiated, process to check if the Source workbook has been updated, and if it has then update the Target workbook. This is a bit complicated but doable. I have some code at work that I can modify and share with you.
But again, look at the solutions I proposed until you get the code implemented. When you have a worksheet that you need to copy data from see ='[Workbook Name.xls?]Source'!A22 (If sheets are NOT in the same workbook) Not the best solution but if you can't wait it is your best bet.