i am attempting to copy a select range of cells that the bottom of th
range of cells changes from one sheet to several sheets. i also hav
three cells that are static at the bottom of the colomn that do no
need to be copied. i know it is simple but for some reason i can't see
to figure it out. any help would be greatly appericated

--
papado
-----------------------------------------------------------------------
papadoc's Profile: http://www.excelforum.com/member.php...fo&userid=3646
View this thread: http://www.excelforum.com/showthread.php?threadid=56226

Two examples - which one you want depends on what gaps may exist in your
data. If it looks like this

1
2

3
4

x
x
x

and you wanted to select cells 1 through 4 (I'm assuming Column A, the x's
are the static rows I assume you want excluded) then:

Sub test()
With Sheets("Sheet1")
.Range("A1", .Cells(.Rows.Count, _
1).End(xlUp).End(xlUp).End(xlUp)).Select
End With
End Sub

Or, if there are no spaces in your data, you could start at the top and go
down, so

1
2
3
4

x
x
x


Sub Test2()
With Sheets("Sheet1")
.Range("A1", _
.Range("A1").End(xlDown)).Select
End With
End Sub

"papadoc" wrote:


i am attempting to copy a select range of cells that the bottom of the
range of cells changes from one sheet to several sheets. i also have
three cells that are static at the bottom of the colomn that do not
need to be copied. i know it is simple but for some reason i can't seem
to figure it out. any help would be greatly appericated.


--
papadoc
------------------------------------------------------------------------
papadoc's Profile: http://www.excelforum.com/member.php...o&userid=36463
View this thread: http://www.excelforum.com/showthread...hreadid=562268

ok..... lets see that didn't do what i needed it too.... let me explain
more.....

in colomn A i have a list of names that changes from day to day that
list starts on cell A4 at the end of the list of names i have 3 cells
one called (This Weeks Total), (Last Weeks Total), and (Difference)

the number of names changes from week to week and i need to be able to
select just the names in that colomn only to copy to several other
sheets. i have the copy code written with no problem but i cant get it
to select just the names in the list only ..... so if you could help i
would be greatful


JMB Wrote:
Two examples - which one you want depends on what gaps may exist in
your
data. If it looks like this

1
2

3
4

x
x
x

and you wanted to select cells 1 through 4 (I'm assuming Column A, the
x's
are the static rows I assume you want excluded) then:

Sub test()
With Sheets("Sheet1")
.Range("A1", .Cells(.Rows.Count, _
1).End(xlUp).End(xlUp).End(xlUp)).Select
End With
End Sub

Or, if there are no spaces in your data, you could start at the top and
go
down, so

1
2
3
4

x
x
x


Sub Test2()
With Sheets("Sheet1")
.Range("A1", _
.Range("A1").End(xlDown)).Select
End With
End Sub

"papadoc" wrote:


i am attempting to copy a select range of cells that the bottom of
the
range of cells changes from one sheet to several sheets. i also
have
three cells that are static at the bottom of the colomn that do not
need to be copied. i know it is simple but for some reason i can't
seem
to figure it out. any help would be greatly appericated.


--
papadoc

------------------------------------------------------------------------
papadoc's Profile:
http://www.excelforum.com/member.php...o&userid=36463
View this thread:
http://www.excelforum.com/showthread...hreadid=562268




--
papadoc
------------------------------------------------------------------------
papadoc's Profile: http://www.excelforum.com/member.php...o&userid=36463
View this thread: http://www.excelforum.com/showthread...hreadid=562268

So your data looks like

A
4 James
5 Robert
6 Joe
7 This Weeks Total
8 Last Weeks Total
9 Difference

This would copy the above names to Sheet2.
With Sheets("Sheet1")
.Range(.Range("A4"), .Cells(.Rows.Count, 1).End(xlup) (-2, 1)).Copy
Sheets("Sheet2").Range("A1")
End With

Since you said in the first post the last 3 lines were static, but the list
was variable, I assumed there could be gaps in between the data. If this
does not work, perhaps post some data and some of your code -otherwise I'm
only guessing at what the data looks like. Is there anything in column A
other than the names and the 3 lines at the bottom?



"papadoc" wrote:


ok..... lets see that didn't do what i needed it too.... let me explain
more.....

in colomn A i have a list of names that changes from day to day that
list starts on cell A4 at the end of the list of names i have 3 cells
one called (This Weeks Total), (Last Weeks Total), and (Difference)

the number of names changes from week to week and i need to be able to
select just the names in that colomn only to copy to several other
sheets. i have the copy code written with no problem but i cant get it
to select just the names in the list only ..... so if you could help i
would be greatful


JMB Wrote:
Two examples - which one you want depends on what gaps may exist in
your
data. If it looks like this

1
2

3
4

x
x
x

and you wanted to select cells 1 through 4 (I'm assuming Column A, the
x's
are the static rows I assume you want excluded) then:

Sub test()
With Sheets("Sheet1")
.Range("A1", .Cells(.Rows.Count, _
1).End(xlUp).End(xlUp).End(xlUp)).Select
End With
End Sub

Or, if there are no spaces in your data, you could start at the top and
go
down, so

1
2
3
4

x
x
x


Sub Test2()
With Sheets("Sheet1")
.Range("A1", _
.Range("A1").End(xlDown)).Select
End With
End Sub

"papadoc" wrote:


i am attempting to copy a select range of cells that the bottom of
the
range of cells changes from one sheet to several sheets. i also
have
three cells that are static at the bottom of the colomn that do not
need to be copied. i know it is simple but for some reason i can't
seem
to figure it out. any help would be greatly appericated.


--
papadoc

------------------------------------------------------------------------
papadoc's Profile:
http://www.excelforum.com/member.php...o&userid=36463
View this thread:
http://www.excelforum.com/showthread...hreadid=562268




--
papadoc
------------------------------------------------------------------------
papadoc's Profile: http://www.excelforum.com/member.php...o&userid=36463
View this thread: http://www.excelforum.com/showthread...hreadid=562268