How to copy the contents from the sub files to the master worksheet based on the id?

**chunkyp** · 04-20-2017, 03:51 AM

Every Friday I receive about 50 excel sheets titled time utilization along with their id (eg: timeuti-1001, timeuti-1002, etc) from my classmates. Currently I am physically copy-pasting the details that they send. I would like to automate this task.
I am saving all the files in C:\timeutilization.

When I click on the ‘Reconcile’ command button in the mastersheet which is in the same folder. I want the macro to identify the student id from timeuti-1001 filename and for that particular student id I would like the macro to match it with the student id cell in the master sheet and copy the contents of the cell for that particular student id.

Eg: Student with filename “timeuti-1007” sends the details this Friday.
pic1.JPG
I would like the mastersheet to look like this:
pic 2.JPG

For every student it needs to update it based on their id. If an user has not sent the details let the row remain blank. Thanks in advance.

**bulina2k** · 04-20-2017, 04:15 AM

Hi there!
From what date shoud the macro bring the contents?
In the timeuti files there are multiple dates..

**chunkyp** · 04-20-2017, 04:21 AM

Every Friday it needs to run.. The Mastersheet should match the date from the system time and timeuti worksheet for each user.

**bulina2k** · 04-20-2017, 05:20 AM

Hello again,

For testing purposes I changed the date in timeuti file to match todays.

Under your button I wrote this code:

Please Login or Register  to view this content.

**chunkyp** · 04-20-2017, 07:51 AM

Thank You Very Much.. It Worked..

I added a new column in my Mastersheet for student name. That is in Column A , and I moved Student Id to Column B.
The individual utilization sheet remains same.

Is this correct:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**bulina2k** · 04-20-2017, 07:55 AM

Yes, it seems ok.
If everything works fine please mark thread as SOLVED.
Thank you for the rep.

**chunkyp** · 04-25-2017, 06:27 AM

I had some problems when I had my friends send me their timeuti files. Mostly they used to change the dates. It also lead to some problems when using the system dates.
Currently I am using a combobox infront of my Reconcile button. Is it possible for me to take the date from combobox and match it with timeuti file rather than the system date?

testnew.JPG

**chunkyp** · 04-26-2017, 02:12 AM

I tried changing the Date keyword to ComboBox, but it doesn't seem to work.

Please Login or Register  to view this content.

**bulina2k** · 04-26-2017, 03:19 AM

Actually you can use the value in the combobox like this:

Please Login or Register  to view this content.

but I don't think that is the problem. Using it like this will not identify it as a date but only as a string. You can give it a try, although I suggest changing the way you choose "friday".

**chunkyp** · 04-26-2017, 03:40 AM

What do you suggest I use "Friday" as? Should I be changing the format?

I added the changes you suggested. I got an error "run-time error:91 Object Variable or With block Variable not set"

**bulina2k** · 04-26-2017, 04:08 AM

Try this way.
I've added a spin button that increments or decrements the date in A1 by a week!
Also a button that brings you the current friday.
Also a check to verify if the date in A1 is fryday.
Take a look pls.

**chunkyp** · 04-26-2017, 04:19 AM

Thanks. This works..
You are the best..

How to copy the contents from the sub files to the master worksheet based on the id?

LinkBack

Thread Tools

Rate This Thread

Display

How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Re: How to copy the contents from the sub files to the master worksheet based on the id?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy rows to master sheet based on contents in column

Macro to auto copy entire row from master to worksheet based on column values

Copy a row from master sheet and paste it to a specific worksheet based on tab name

need to copy some cells contents to a another worksheet based on a value condition

Copy data from several files to one worksheet based on file names

Help w/ macro copying cell contents on Master to new worksheet based on button location

Drop Down List to Copy Entire Contents of Worksheet into Master Worksheet

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions