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

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")

"David" wrote:

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

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

"David" wrote:

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

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

--
Regards,
Tom Ogilvy

"David" wrote:

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

On Jul 1, 7:12 pm, Mike wrote:
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