Maybe...

Dim SourceSheet as Worksheet
dim SourceRng as range
dim LastRow() as long
dim i as long

dim DestSheet as worksheet
dim destcell as range

'something that sets those variables...
'lastrow() and i

set sourcesheet = worksheets("sourceworksheetname")
set destsheet = worksheets("destworksheetnamename")

with sourcesheet
set sourcerng = .range(.cells(firstrow(i),1),.cells(lastrow(i),las t_column))
end with

with destsheet
'just use the top left cell of the range
'excel will adjust the range -- just like it does when
'you do it manually
set destcell = .cells(2,1)
end with

sourcerng.copy
destcell.pastespecial paste:=xlpastevalues
destcell.pastespecial paste:=xlpasteformats

===========
All untested and not compiled. I didn't take the time to set up those other
variables. So watch out for typos!



MDubbelboer wrote:

Dave Peterson Wrote:
How about using copy|paste special|values, followed by paste
special|formats
Alright. I'm making this harder than it should be:
what is wrong with the following

SourceSheet.Range(SourceSheet.Cells(firstrow(i), 1),
SourceSheet.Cells(lastrow(i), Last_Column)).select
selection.copy

DestSheet.Range(DestSheet.Cells(2, 1), DestSheet.Cells(lastrow(i) -
firstrow(i) + 2, Last_Column)).select
selection.pastespecial paste:=xlPasteFormats, Operation:=xlNone,
skipblanks:=False, Transpose:=False
selection.pastespecial paste:=xlPasteValues, Operation:=xlNone,
skipblanks:=False, Transpose:=False

it was able to copy and paste when I had it as .value but when I try
this method it's not doing anything...

--
MDubbelboer
------------------------------------------------------------------------
MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
View this thread: http://www.excelforum.com/showthread...hreadid=519666

--

Dave Peterson