finding a particular cell in another worksheet whose row number ch
 

Hi I'm new to macros. I've been trying to make a workbook that I can use at
work to track the progress of numerous projects. I have created a master
copy which will be modified for several different projects. When milestones
are reached the owner of the projects records this in the sheet in a colum
named "Done". This is compared to another column called "planned" and a
percentage can be made called "adherance to planned" This is then tracked for
each month for each ROW, (as each row will represent a new component of the
project).

In another sheet I devised a formatted repot to summaris the data. I got
this working, even made a macro to do it, But my problem is in worksheet 1,
extra lines will be added/deleted and the cell reference for the SUM of the
rows above will constantly have a different row address (same column). I
would like to up date my macro so that it copies a range of cells at the
bottom of my spreadsheet.

Any ideas would be appreciated.
--
agibson

--
agibson

I like to pick out a column that always has info in it if the row is used. Then
use that to determine the rows to copy.

For instance:

dim LastRow as long
dim fwks as worksheet
dim twks as worksheet
dim rngtocopy as range
dim destcell as range

set fwks = workbooks("otherworkbook.xls").worksheets("Sheet1" )
set twks = workbooks("summaryworkbook.xls").worksheets("Sheet 1")

with fwks
lastrow = .cells(.rows.count,"A").end(xlup).row
'skip the header in row 1 and copy through column F
set rngtocopy = .range("A2:F" & lastrow)
end with

set destcell = twks.cells(.rows.count,"A").end(xlup).offset(1,0)

rngtocopy.copy _
destination:=destcell


==========
And if you put your formula in row 1 (always visible with windows|freeze panes),
you could just use the rest of the column:

=sum(B3:B65536)

And never worry about that address.


Andrew wrote:

Hi I'm new to macros. I've been trying to make a workbook that I can use at
work to track the progress of numerous projects. I have created a master
copy which will be modified for several different projects. When milestones
are reached the owner of the projects records this in the sheet in a colum
named "Done". This is compared to another column called "planned" and a
percentage can be made called "adherance to planned" This is then tracked for
each month for each ROW, (as each row will represent a new component of the
project).

In another sheet I devised a formatted repot to summaris the data. I got
this working, even made a macro to do it, But my problem is in worksheet 1,
extra lines will be added/deleted and the cell reference for the SUM of the
rows above will constantly have a different row address (same column). I
would like to up date my macro so that it copies a range of cells at the
bottom of my spreadsheet.

Any ideas would be appreciated.
--
agibson

--
agibson

--

Dave Peterson