Change the code to look something like this. I added the additional testing
to make sure you never accidentally grab rows ABOVE row 7 if things happen to
be empty for some reason:

Dim lastRow As Long

lastRow = Sheets("Raw Data").Range("A" & _
Rows.Count).End(xlUp).Row
If lastRow < 7 Then
lastRow = 7
End If
Sheets("Raw Data").Range("A7:A" & _
lastRow).Copy Destination:=Range("A2")

variable lastRow will be set to the largest numbered row on the Raw Data
sheet that has either a formula or value in it; with 7 being the smallest row
number it will pick up with the check we put into the code.


"James" wrote:

I have this line of code and it works but it is dependent on having 100 rows.
I would like to change it up so that it will not be dependednt on having a
certain amount of rows.

Sheets("Raw Data").Range("A7:A100").Copy Destination:=Range("A2")

It is just copying data from the worksheet "Raw Data" to another sheet in
the same workbook. I just never know how many samples I have and would like
to change the code to be a little more flexiable. I thought the change would
of looked like this:

Sheets("Raw Data").Range("A7:A").Copy Destination:=Range("A2")

This didn't work though.

Thanks