Macro to transfer data from one sheet to other

[paul_sykes00]

Hi,

Hope someone can help me with this. Its pretty complicated so i'll be impressed

I have a time sheet for each member of staff. Please see attached examples "Tech 1 - Time Sheets" and "Tech 2 - Time Sheets". The only differences between the 2 will be:
1) Staff Name (cell D3)
2) Job Number (column A)
3) Job Name (column B)
4) Job Type (column C)
5) Hrs spent on job (columns E to K)
6) File name (column E)

I also I have a series of service reports. Please see attached examples "10001(S) - Job 2" and "10001(S) - Job 2". There layout will be identical

I need a macro in each of the time sheets which will open the file name in column L and transfer all the information to the service sheets.

For example i'll open a time sheet, run the macro and starting with row 16 it will open the file given in cell L16, extract data in cells A16:L16 and place data in the service reports as highlighted in the red cells in the service reports. After row 16, the macro would move onto row 17 and onwards until all data is entered. Once the macro has been run the first time sheet, the same macro would be run in the 2nd timesheet. The result should be that the data will be entered as highlighted in the red cells of the service report (E47:I65).

For now we can say all the files are saved on say C:\ drive

Thanks in advance for your help,

Paul

[OllieB]

Paul, I am working on something for you

[OllieB]

Try the attached workbook which acts as a central console holding the main routine. Just specify the directories holding the timesheets and service reports (don't put them all in the same directory!) amd click on the button. The solution will automatically process all workbooks from the timesheet directory and for each timesheet, update the corresponding service reports in the other directory. There is no need for coding in either the timesheet or service report workbook.

Please test carefully and adjust where necessary

[paul_sykes00]

Thanks very much for your response Ollie. Just seem to be having a few problems trying to get it to work.I tried to test it using the sample files that I attached in the email. it seems to get as far as opening time sheets and then nothing and I can't figure out why. Any suggestions?

Also is it possible to amend to change a couple of your assumptions:

1) you assume each time sheet will only have one tab. In fact each time sheet will have 52 tabs. One for each week of year and will be titled with date of week ending. So say the week ending is the 25th January 2012 and the tab in all time sheets is named "25-01-2012", can this name be entered into the console so only this tab in time sheets is updated?

2) if time sheets/service reports are saved in different sub directories of a specific folder can the macro be updated to search for the file?


Thanks again for your time and effort on this one.

[OllieB]

Paul,

The routine looks for the first worksheet in the timesheet workbook. In the examples provided by you the first worksheet was actually a hidden worksheet for a "real" employee which was empty. I have removed those hidden worksheets for testing purposes, and made sure that the only remaining worksheet had valid entries on it.

question 1:
only process worksheets from the timesheet workbooks where the name equals the entry specified on the CONSOLE tab - yes this is possible, included in the attached workbook

question 2:
possible, but this would require a fair amount of coding (a recursive routine to search all sub folders) - not yet included in the attached version


Lets try and test this one first

[paul_sykes00]

Fantastic. Very impressed with a few tweaks this could be a very useful tool.

Regarding point 2 on my previous message. I can update my file structure so all time sheets are in the same folder but unfortunately my service reports will always be in a directory with up to 3 levels of sub directory within it.

The only other change which would make it perfect would be to deal with the problem where the data entered in the service sheet exceeds row 64. A solution in my mind would be to have hidden rows between rows 64 and 65 which are unhidden when row 64 is reached. Alternatively make the macro create additional lines as required.

Thanks very much Ollie for all your time and effort on this. I'm pretty new to macros but beginning to realise they can be very useful tools.

[OllieB]

Paul,

I have made it possible to also search (unlimited) sub folders!

I already expected a problem with the limited number of rows available on a service report. I have looked at inserting rows and copy&paste new rows but because of the formatting and formulas this becomes a major pain in the butt. The easiest way would be to insert (lots) of empty rows which would be hidden. When the import process needs an additional row, it would just unhide the rows needed. No problem making this change. Will you deliver a new/revised copy of the service report with the hidden rows in place so I can adjust the coding accordingly?

the sub_folder search version is attached

[paul_sykes00]

Amazing. Thanks very much. I've added a modifed service report so hidden lines can be revealed. Thanks again.

Anyway time for my weekend to start. I look forward to testing it when I return on tuesday. Have a great weekend

[OllieB]

Paul did you notice that all formulas in columns A, B and C are now invalid?

[OllieB]

Paul, I have made some slight changes to the service report template and changed the program. Test carefully.

[paul_sykes00]

Hi Ollie,

I just seem to be having a couple of problems with the latest version. For some reason it is still not revealing the hidden cells. Still can't make it search sub directories either. I've tried putting the main directory into the console but it still doesn't recognise files saved in any subdirectories.

Cheers

Paul

[OllieB]

Paul, I will check but it may be tomorrow before I have time

[paul_sykes00]

Thanks Ollie, that would be great. Hopefully its only something simple

[OllieB]

Paul,

test 1:
I have created a directory structure
..\Service Report Main\Timesheets\A
..\Service Report Main\Timesheets\B

and have specified ..\Service Report Main\Timesheets as the directory holding the timesheets.

NO PROBLEM, all timesheets were found in the both the A and B directories

[OllieB]

Paul,

test2
Updated the service report templates, i.e. processing all timesheets for the worksheet name (date specified).

NO problem, all records were processed

test 3

Too many time sheets, i.e. not enough rows on service report.

PROBLEM found - and corrected

I have attached the corrected version. Please check again. Note that I could not find any problem finding the timesheets in the sub-directories. I hope you do not mean that the service reports will be in different directories.

I can also think of a way to speed up the process, but would need some more time for that

[paul_sykes00]

Ah, I see. Its my service reports that are in different places. Can I swap the coding so service reports can be found from sub directories. Cheers

[OllieB]

Paul, your suggestion is not going to possible. The process starts with a search for all timesheets, and then for each timesheet it attempts to open the corresponding service reports. You cannot simply swap the two types of workbooks around. However the speeding up I was talking about had to do with not opening a given service report multiple times if multiple people worked on the same job. In that logic the service report would be opened only once if there is at least one time entry against it. In the same change I cannot also "search" for the servicereport. It will take me some time to change though, and I do not have much time today. I will see what I can do.

[paul_sykes00]

Haha, i might have guessed my request wasn't going to be easy. No rush but if you can come up with a solution it would be greatly appreciated.

Cheers

[paul_sykes00]

Out of interest what change did you make to the service call template. I need to make that change to my actual template. Cheers

[OllieB]

You inserted all your new rows into the last row shown, which caused the concept of two merged rows per cell in columns a, b and c to fail. I have also inserted new rows at the end but in blocks of two rows. Unhide the rows in my version and yours and you will see the difference. On your previous post, of course we will find a solution!

[OllieB]

Hi Paul,

I have re-written the code and no the solution also supports service reports in sub-folders. The solution will now also open a specific service report only once when it is mentioned on different timesheets, so overall performance should be better as well. I having some (corruption) problem with my Excel instance at home so testing has been somewhat limited, so let me know.

[paul_sykes00]

Hi Ollie,


I've used coding similar to that provided below for searching for my service reports in a different macro. Could this be modified to work within this macro. I guess the only difference for this case was that the file path (Q3) and the file name (Q2) were in same sheet.

Please Login or Register  to view this content.

Any use?

Thanks

[paul_sykes00]

Ah hadn't noticed your version 5. I'll test that now and let you know.

[OllieB]

Paul, any results as yet?

[paul_sykes00]

Hi Ollie,

Sorry about the delayed response. Not had chance to test it until today.

I seem to be having a few issues. I'm sure the solution is simple but understanding the code is not so simple, haha.

I made some very small amendments to the service report and now the thing refuses to work. The columns the date is copied into have change slightly and I updated this accordingly in the macro but it doesn't seem to work anymore. I've attached the sheets

Thanks

[OllieB]

The first problem appears to be the formula you have added to column L on the timesheet. This formula results in invalid (and non-blank) entries like #N/A, "- .xlsm". The current logic regards any non-blank value as a file name so that would probably result in a lot of errors messages. Are you getting any other error messages?

[OllieB]

I also noticed that you have shifted the columns on the service report (Labour Cost)

[paul_sykes00]

It doesn't even seem to make it as far as the N/A. These can be deleted for test run as they link to a separate spreadsheet that looks up values to provide correct file names.

I moved labour costs but I changed this column reference in the macro to reflect


For some reason it only seems to open the first file listed in the time sheet and it then pastes the values much lower down in the spreadsheet

[OllieB]

Hi Paul,

as you have not posted the master workbook that contains the amended code, I cannot see what you have changed. I have maniuplate the timesheet that you have posted so that column L only contains valid service report workbook names. Ran the attached version and all updates were correct.

[paul_sykes00]

Hi Ollie,

I think we are nearly there with this now. Just a couple of small issues

1) If a rerun the data for a second week. this now overwrites the data in service reports. This never happened in previous versions. Not allowing duplicate data would be good but it definitely can't overwrite non duplicate data

2) The column in timesheet with the file name actually has a look up formula to put the file name. Although it set up to return a blank value if no job number is provided the macro seems to notice that there is data in this cell. The result is that it will only read the first file name and not read subsequent ones. If formulas are deleted from cells that include data it works fine.

Hopefully we can resolve these last 2 issues. Cheers

[jeffreybrown]

Hi Paul,

Administrative Note:

• Please see Forum Rule #3 about code tags...
• Added for you this time in post #22, but please use them in the future…Thanks.

[OllieB]

1) If a rerun the data for a second week. this now overwrites the data in service reports. This never happened in previous versions. Not allowing duplicate data would be good but it definitely can't overwrite non duplicate data

I have check the code and ran another test. I cannot replicate this problem. The solution looks at the last row filled in column E (=Name) on the service report. The only problem I can see is if there is no name on the timesheet in cell D3.

2) The column in timesheet with the file name actually has a look up formula to put the file name. Although it set up to return a blank value if no job number is provided the macro seems to notice that there is data in this cell. The result is that it will only read the first file name and not read subsequent ones. If formulas are deleted from cells that include data it works fine.

I already reported this problem in my previous post. If the solution cannot look at column L to determine if it needs to transfer entries to a service report, please advise what logic you propose. I cannot look in column A because also non-chargeable entries are listed, same goes for column B. I therefore suggest you change your formula in column L so that it only shows a valid worksheet name in case there is one and blanks if there is no service report to update.

[paul_sykes00]

Hi Ollie,

I've attached the exact files i'm using for the test:

1) Still having the issue with the macro overwriting data. Try running it with the attached files using "08-01-2013" and "15-01-2013"

2)Column L now blanks if there is no service report to date. Unfortunately the macro still doesn't like this and only opens the first file on the list. Maybe only way around this is to make the macro copy row L and paste it into row Q as just values.

Hopefully almost there. Thanks

[OllieB]

Paul,

- there was a problem identifying service reports in column L on the timesheet - this has been corrected
- because of ' characters in empty cells in column E on the service report the solution is unable to determine the last line used - the cells MUST be empty
- unable to replicate the overwrite problem reported - did not happen in my tests

Please try with revised version attached

[paul_sykes00]

Awesome. Part 2 solved.

Still have issues with part 1. Just can't figure out why the macro is overwriting when I run it but not when you run it. We are using exactly the same files. Very frustrating.

[OllieB]

Paul, only way to solve this is setting breakpoints and debug the code while running on your side.

In particular the following statement is of interest when running the import for the 15th.

Please Login or Register  to view this content.

The value in lngNextRow after completion of the above statement should be the last line number filled by timesheet 08-01 as it will increment for each new line found.

You could also check whether the save changes after having completed import 08-01 is successful, before starting the import for the 15th.

[paul_sykes00]

Hi Ollie,

Just had a chance to look at this and figured out how to stop making it overwrite data. For some reason when I was going to Line 96 of column E in the service report and using End(xlUp).Row it was always going to the header instead of the bottom row of text like you would expect. If I change the macro to use the date column G it works as you would expect. I can only presume it is due to some form of corruption in my spreadsheets as your code works perfectly. Thanks very much for your help on this. It appears to be working exactly as I hoped. At first I wasn't sure if this is even possible so thanks for all your time on this. I still need more time to fully understand the coding but definitely picked up a few worth while tips. Thanks again for all your efforts and expect a new challenge in the near future. Haha,

All the best,

Paul

[OllieB]

Hi Paul,

Good to hear from you and thanks you for the update. The problem you described sounds strange, but I am glad to hear you managed to find and resolve it. May I please ask you to mark the thread as SOLVED, and to click on the star icon (on the left side below my name) if you are happy with my contribution. Thank you!