Avatar of Mohammed Hamada
Mohammed HamadaFlag for Portugal asked on

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

* Excel Table* Excel templatesMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Robert Berke

8/22/2022 - Mon
Roy Cox

You need to attach examples of the workbooks.
Mohammed Hamada

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
Roy Cox

Images don't really help and as you want VBA help I haven't time to attempt to create workbooks.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ugra Narayan Pandey

1. Copy and paste the link
  • 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.
Ref: https://www.got-it.ai/solutions/excel-chat/excel-tutorial/data-entry/automatically-update-data-in-another-sheet
Mohammed Hamada

@Roy, I am attaching file samples below.


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 
Robert Berke

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mohammed Hamada

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 .

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

Robert Berke

I will get to this later today.

Robert Berke

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Robert Berke

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Mohammed Hamada

How to run this macro automatically instead of pressing on it through the app?

thank you
Robert Berke

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.


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.