[Urgent] Inserting Time sheet Data in to Master sheet Based On the Condition Using Macros.

Hi Guys,

I have a Master sheet(Excel Sheet) which contains the columns Resource name, No: of Hours, File names and Mail id. In this sheet, i have two types of employees. ie., Permanent and temporary. we can identify the employee type based on their file names.(time sheet names). for example, permanent employee's file name will have a format such as "TS-KKCMania-hyd-" and temporary employee's file name will have a format such as "KKCMania". All the time sheet's of temporary employees are available in the master sheet itself. but, i need to fetch the permanent employees's time sheet's from other location.

The problem here is, i need to display all the time sheet's of both the temporary and permanent employye's in the same order as they were displayed in the master sheet(as like the order in Resource Name column) followed by the Consolidate Sheet.

The Sample Data in the below link will give you a more clear picture.

Its better you attach the sample excel file here instead of having us download it from an external site.

Here are the Time sheet files related to Permanent employees.

The Remaining two Time sheet files related to permanent Employees are as follows. please find the attachments.

I hope there is no confidential data in your files.

I Got u. i forgotten to change something in my files. if u don't mind, i will do it and upload the files agian. seriously, i didnt observed it in the first instance. Thank you...

So you need the data to be filled in from the individual files into the master sheet? Or do you want the individual files to be copied and pasted as individual sheets in the master?

Working on this. Sorry, couldnt do it earlier.

All the files for the permanent employees will be stored in one folder right? Will there be files for the temporary employees in the same folder?

You have given the file names for the permanent employees in column D, where would the temporary employees' info be listed?

Ok i will work something out for you.

Try this code - extremely sorry for the delay -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Your file path shows Kishore\/Desktop. Check the file path properly in the code.

I am sorry, the V looked like \ and /. The macro picks up the file based on the name in column D. Is it blank for that row?

And which line is highlighted in the code when the error occurs?

Whats the exact text of the error?

Check that the filename in the excel sheet and the filename of the file in the folder match properly, spelling, case, etc. The error states that the file couldnt be found.

Is it always the last entry in the column that is giving you the error?

So the file is present in the folder but the macro is not picking it up to be added to your file right?

Let me check again.

I got the error when Vinodini was not found in the folder. So since the file was not present, it gave an error.

How do you want to handle this?

Let me duplicate one of my files and name it as Vinodini and try, since i dont have the Vinodini file.

Ok, i found out the issue. You have the column with filenames which can have entries either as KrishnaMohan or TS-Karanvijay-hyd-. However the sheets dont have the TS against them. So we can sort this out in 2 ways - either have another column giving the list of sheet names or name the sheets as per the entries in the Filenames column (C). How do you want to handle this?

But since you are copying the file as a whole from the other files to your main file, the sheet name will need to be changed in those source files.

Your updated code is here

Please Login or Register  to view this content.

There was a space after the text in cell D15 in the Consolidate tab, hence the error. Put the sheet names in column F and then run the code.