Try this. UN tested
Sub_Copy_Discrepancy_Tools ()
with Sheets ("INV")
dest=.Cells (Rows.Count, "A") .End(xlup) (2, 1)
If .Range ("A39") < "Shortage" Then
Sheets ("Discrepancy Tools") .Range (A1:P201") .copy dest
Else
Sheets ("Discrepancy Tools") . Range ("Q1:AF266") .Copy dest
end if
end with
End Sub.


--
Don Guillett
SalesAid Software

"Sam Fowler" wrote in message
...
HI I posted this earlier, but wasn't very clear as to what I am trying to
do. I have a macro that selects data on a work sheet and qualifies what
additional data to copy to the bottom of the report. Some of the rows on the
first sheet may be sized differently and I would like to select all rows
after the last cell containing data and set the row height to 12.75, before
pasting the copied data. I would actually like to set this on at least 1ooo
rows, if not all of the way to the bottom.

I would like this to happen immediately after last cell is selected and
before the paste operation.

Thanks for any help on this,
Sam

This is the current code I am using

Sub_Copy_Discrepancy_Tools ()

Sheets ("INV") . Select
Range ("A39") .Select
IF Range ("A39") < "Shortage" Then
Sheets ("Discrepancy Tools") .Select
Range (A1:P201") .Select
Else: Sheets ("Discrepancy Tools") . Select
Range ("Q1:AF266") .Select
Endif
Selection. Copy

Sheets ("INV") .Select

Dim LastCell = Worksheets ("INV") .Cells (Rows.Count, "A") .End(xlup) (2,
1)
Lastcell.Worksheet.Select
LastCell.Select
Activesheet.Paste

End Sub.