Loop Through A Range, Find Cells > 1, Copy Offset To Another Sheet

[bugmenot]

Hi There,

I have been searching all day and can't find a piece of code that will do what i am looking for. I am desperate for help.

I am looking for some code to loop through a range (F4:L30) on Sheet 1, find any cells that have a value > 1, then copy the value of Row 3 in the column in which each of these cells are found to Sheet 2 Column F.

Thanks in advanced.

[Paul]

Hi Bug,

Can you elaborate a little? What if multiple values greater than 1 are found in F5, F7, F8, F10 and F18? Do you want F3 copied to Sheet2!Fx five times, or just once since there was at least one instance greater than 1?

In turn, then, if a value greater than 1 is found in columns F through L, do you simply want the value from F3 entered into the next available cell in Sheet2 column F? Followed by G3 in the next row down, then H3, etc.? Or do you want to overwrite F1:Fx every time?

[bugmenot]

Hi Pjoaquin,

Thanks for the quick reply. Sorry about the lack of info.

You are correct. If multiple entries do exist in column F in the range specified, i would like multiple copies to go to sheet 2.

However, it does get complicated. If multiples do exist, values in column B in the same row as the searched range will have to be copied the same number of times. Sorrty to add more confusion.

Thanks

[royUK]

Are you sure you want to copy the same row multiple times? here does offset come into it?

[bugmenot]

Hi Guys,

Thanks for the help. It sounds confusing for me, so i can only imagine what you guys are thinking. I have attached the excel file so you can see what i had in mind.

It is a timesheet that lets employees enter a client, job, and activity. Following that, they enter the number of hours they spent on what day. Considering that someone can spend time across dates on a single client, job, and activity i do indeed need to duplicate these entries.

What i am trying to achieve is that once the week is up, the employee can hit "update", and all the relevant info will get copied into a more friendly format for analysis by another excel file.

Thanks.

[bugmenot]

HI Guys,

I guess offset doesn't really need to be involved, as the cell reference that contains the date is in the same position all the time. I am fairly new to VBA, so i thought offset could be used as the row number increases to reference back to this. However, thinking about it now, it wouldn't be neccesary.

Thanks.

[rylo]

Hi

Given the example file data, what should your data backup sheet look like? Where does the employee name come from?

rylo

[bugmenot]

Hi Rylo,

Sheet number two is the "data backup" sheet that has all the relevant information e.g. employee, activities, jobs, units and hopefully date. The employee name links to a cell up the top of the timesheet (F1), but i just removed it to post up on the forum for privacy reasons. The vba on sheet 2 will autofill the employee name based on what is entered in F1 on sheet 1.

Thanks in advance