Automation of filling up an excel sheet from another excel file.
I have a main report excel sheet file that I update on dailybasis with emails that exceed limits of how many emails they are supposed to get per day.
Those users are downloaded from a daily report in a single file in a common cloud location with the main report file.
What I would like to be able to do is once I downloaded the daily report file into the same folder location, the main report file would automatically get the details into "Specific column" from the daily report file.
More details:
dailyreport_30-9-2021.xls would have 6 column that only two of them are of use for me (Recipient, Date).
MainReport.xls would have many columns each with the certain date.
29-9-2021, 30-9-2021, ..etc
I would like Excel to be able to get the Recipient data from the dailyreport file into the column of that particular date in the main report file.
Is this possible to automate?
Thank you very much
Last Comment
Roy Cox
You need to attach examples of the workbooks.
ASKER
Example
DailyReport-9-30-2021's recipient cells should go under the column 9-30-2021's cells in alphabital order in the Main report. that's what I need
Main one has columns with dates on them. and each of these columns have these email addresses that are supposed to go there depending on the daily report date.
DailyReport looks like this and its file name is report_9-30-2021
Main Report
Thank you
DailyReport-9-30-2021's recipient cells should go under the column 9-30-2021's cells in alphabital order in the Main report. that's what I need
Main one has columns with dates on them. and each of these columns have these email addresses that are supposed to go there depending on the daily report date.
DailyReport looks like this and its file name is report_9-30-2021
Main Report
Thank you
Images don't really help and as you want VBA help I haven't time to attempt to create workbooks.
1. Copy and paste the link
2. Enter the formula manually
- From the source worksheet, select the cell that contains the data or that you want to link to another worksheet, then copy it by pressing the "Copy" button on the "Home" tab or by pressing CTRL + C.
- Go to the destination worksheet and click the cell you want to link the cells to in the source worksheet. On the Home tab, click the Paste drop-down arrow button and choose Paste Link from "More Paste Options". Or right-click the cell in the target worksheet and select Paste Link from the Paste options.
- Save your work or go back to the source workbook and press the ESC button on your keyboard to clear the border around the copied cells and save your work.
2. Enter the formula manually
- In the target worksheet, click the cell that will contain the link formula and enter the equal sign (=)
- Go to the source worksheet and click on the cell containing the data, then press Enter on your keyboard. Save your work.
ASKER
@Roy, I am attaching file samples below.
daily-sheet-09-30-2021.xlsx
main.xlsx
@Ugra
The problem in this solution you provided is that the source file is static and in my case it changes every day so it's not static and therefore the process will still depend on me changing the source link everyday.
If I can get the destination file to read from a file with specific name format "day date.xls" that would be perfect.
Thank you
daily-sheet-09-30-2021.xlsx
main.xlsx
@Ugra
The problem in this solution you provided is that the source file is static and in my case it changes every day so it's not static and therefore the process will still depend on me changing the source link everyday.
If I can get the destination file to read from a file with specific name format "day date.xls" that would be perfect.
Thank you
How do you want your Main report to be sorted?
I am very uncertain about "count of identical".. If it is the main sort key the report becomes very messy. I have modified your test data and attached it.
The pivot table highlighted in green is understandable,
But if we sort on count, things get very messy. The cells highlight in yellow illustrate the mess.
If count of identical is not the sort order what do you want instead? Once I understand your goals the vba and automation should be straight forward.
ee sept 30.xlsx
I am very uncertain about "count of identical".. If it is the main sort key the report becomes very messy. I have modified your test data and attached it.
The pivot table highlighted in green is understandable,
But if we sort on count, things get very messy. The cells highlight in yellow illustrate the mess.
If count of identical is not the sort order what do you want instead? Once I understand your goals the vba and automation should be straight forward.
ee sept 30.xlsx
ASKER
Forgive me I am not an expert on Excel but I would like that columns to be sorted alphatabically each depending on the emails in the column.
I think I also made a mistake by naming that column "Count identicals" as each row's formula counts any cell that has any data in it in that row.
I want that all the emails in the Source (daily-sheet-10-01-2021) file to be copied to its similar value in that destination file in the column pertaining date to the source file .
Example:
Source file daily-sheet-10-01-2021 has two columns with email buser3@hotmail.com, me@gmail.com, auser2@gmail.com
Target file main.xls should take this data and place them into the same row of each email but in the column of date 10-01-2021.
Here's a screenshot of how I would like to do it.
Thank you very much
I think I also made a mistake by naming that column "Count identicals" as each row's formula counts any cell that has any data in it in that row.
I want that all the emails in the Source (daily-sheet-10-01-2021) file to be copied to its similar value in that destination file in the column pertaining date to the source file .
Example:
Source file daily-sheet-10-01-2021 has two columns with email buser3@hotmail.com, me@gmail.com, auser2@gmail.com
Target file main.xls should take this data and place them into the same row of each email but in the column of date 10-01-2021.
Here's a screenshot of how I would like to do it.
Thank you very much
I will get to this later today.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If your company requires digitally signed macros, you should talk to your IT department. They can generate a "selfcert" certificate for no charge, then use that to sign the macro. If they don't know how they can see https://support.microsoft.com/en-us/office/digitally-sign-your-macro-project-956e9cc8-bbf6-4365-8bfa-98505ecd1c01
ASKER
How to run this macro automatically instead of pressing on it through the app?
thank you
thank you
There are many ways to automate this.
For instance, to have the macro run automatically, add the following code under main.xlsm ThisWorkbook
Private Sub Workbook_Open()
Call DailyUpdate
End Sub
main.xlsm has a few other minor changes to the original macro. see if you like the results.
rberke
P.S. if you want to learn more about Excel VBA keep asking questions. For instance the above Workbook_Open code will be run whenever Main.xlsm is opened. Excel VBA supports dozens of such "Events" which can programmed to do some amazing things.
For instance, to have the macro run automatically, add the following code under main.xlsm ThisWorkbook
Private Sub Workbook_Open()
Call DailyUpdate
End Sub
main.xlsm has a few other minor changes to the original macro. see if you like the results.
rberke
P.S. if you want to learn more about Excel VBA keep asking questions. For instance the above Workbook_Open code will be run whenever Main.xlsm is opened. Excel VBA supports dozens of such "Events" which can programmed to do some amazing things.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
