Macro to take data and insert in a different spreadsheet

Hi everyone. Hopefully one of you can help me out. I have added an attachment to help.

In simple terms if you open the attachment, I need a macro that:

1) opens a 2nd spread sheet that contains sheet 2
2) takes data from sheet 1 and adds it to sheet 2 (sheet 3 is how sheet 2 should look after macro has added data.

Thanks in advance

Please Login or Register  to view this content.

Hi AB33. Thanks for your response but its not quite what i'm looking for. Your code works perfectly for the example given but I need to be more flexible so whatever the date the macro finds it a pastes the job number, job name, type and hours onto the next available line.

I have attached another example to help clarify this. So on sheet 1 I now have 9 lines of data. Lets assume we enter these lines of data one at a time. After each line is entered we run the macro. After running the macro 8 times I would now want sheet 2 to look like sheet 3.

Thanks

Hi Paul,
Sorry! I do not know how to code your needs, but others may know it. I know how to code it using if statement, or select case, but this assumes that you have only a handful of dates(let's say less than 14 days), otherwise the code is going to be Cumbersome, catering for each day of the year( If your data extends for the whole year). I do not imagine your data is 10 rows- this is just a sample? Right? I have tried to use the find and match functions, but I am unable to get the right columns.

Hi Mike7952,

Thanks very much for your response. I'm definitely getting much closer to what i'm looking for. A couple of questions though

1) Code assumes that Job number in order - is it possible to adjust it so this doesn't matter.
2) If I run it twice it repeats the data - ideally i need it so if I update sheet 1 with new data and then run the macro it doesn't repeat the original data in sheet 2
3) Sheet 2 will actually be located in a different spreadsheet (i.e c/timesheet.xls). This spreadsheet will include a different tab for every week of the year (example attached). The file path and sheet name will be included in column F of sheet 1. Is it possible to update the macro so it opens the correct spreadsheet and then runs the macro.

Thanks very much.

Hi Mike7952,

I don't suppose you have any further thoughts on the above?

Thanks again

Paul