Coping ranges of cells

[David]

I have a worksheet which has hours worked by employees on various
projects over the last year. So, column 1 is the employee name, column
2 is the project name and column 3 starts with Jan 1 and continues on,
so the number in column 3 represents the hours that the employee
(named in column 1) worked on the project (named in column 2) on Jan
1, and column 3 is the hours worked on Jan 2, etc.

I have to write a routine that copies all the data for a given
employee for dates between some start date and some end date to a new
worksheet in the same workbook. This new worksheet will have column 1
being employee name, column 2 being project, column 3 being the hours
worked on the start date, etc.

What is the most efficient way to copy ranges of rows and columns to
the new worksheet?

Thanks,

David

 

[Guest]

When you talk about efficientcy I assume you would mean NOT using the clip
board. Doing a COPY and then a PASTE uses the clip board. I prefer using
the COPY weith the destination in the same statement


range("A3:B7").copy destination:=sheets("Sheet2").range("D5")

 

[Guest]

Your saying Column 3 has hours worked and the day they worked??

 

[Guest]

Something along the lines of

Dim rng as Range, rng1 as range
Dim rng2 as Range
With Worksheets("Sheet1")
set rng = .Range(.cells(2,3),.Cells(rows.count,3).End(xlup))


res = Application.Match(clng(DateValue("Jan 8, 2007")),rng,0)
if not iserror(res) then
set rng1 = rng(res)
res = Application.Match(clng(DateValue("Mar 2, 2007")),rng,0)
if not iserror(res) then
set rng2 = rng(res)
.Range(rng1,rng2).Offset(0,-2).Resize(,4).copy
Worksheets("Sheet2").Range("A2")
end if
End if

End With

 

[David]

Your saying Column 3 has hours worked and the day they worked??

Mike,

I wasn't very clear, sorry. Assume a header row with labels: Employee,
Project, Jan1, Jan2, Jan3, ...
Now starting from row 2 you have data like John Doe, ProjectX, 2, 3, 7
2, ....
This particular record (row) would mean that John Doe worked on
ProjectX for 2 hours on Jan 1, for 3 hours on Jan 2, for 7 hours on
Jan 3, ...

Joel,

You suggested range("A3:B7").copy ...Thanks, but if I know I want to
copy rows 3-7 columns A, B, G and H, say, it would be more convenient
to you R1C1 format for the range, but I haven't been able to figure
out how to do that with the range.copy method. Help please.

Tom Ogilvy,

Wow. You were way over my head with your code -- I've got a lot to
learn. I tried looking up Application.Match but it Match was not
listed as a method of the Application object, nor could I find
anything on clng. Little more help please.

David