Have you tried recording a macro doing this to see what the code  looks
like??

if that is going to be a set range  you just have to select that range.. and when
you go to paste it.. use the pastespecial data only option... if you want more
i'll play a little and post some code  for it.. if it's a set range you can
also name that range to make it easier to select and copy... if however that
is only a maximum  and you want to copy  only available data in that range
dynamically.. then you'd have to use conditional formatting and get the last row
used for the select statement..

Sub Macro1()
Range("A2:N22").Select
Selection.Copy
Range("A46").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

End Sub


This is what the code  looks like , the range  ( A2:N22 ) is always fixed
but sometimes the data only available ,say at A2 : N14 , so I would like
to copy  only this only without carrying the whole range ( A2:N22 )

then i would do this...

sub macro1()
dim irow as integer
irow = Range("N22").end(xlup).row

range("A2:N" & irow).select
Selection.copy
range("A46").select
Selection.pastespecial paste:=xlpasteall, operation:=xlnone, Skipblanks:= _
False, Transpose:=False
end sub

that works if you change:

irow = Range("N22").end(xlup).row

to

irow = Range("N23").end(xlup).row

If you don't make that change, then you will copy  only the first
line (row 2) when there is data in row 22.

that's what i get for copying.. i new that.. just didn't change the
value... good catch.. thanks

No problem. I learned something from your code  (I wasn't familiar
with ".end(xlup)"), so thanks for listing it.

if you play with it you'll see theres xlup, xldown, right and left as well...i
think there is more options but can't think of them right now... and that's how
i found out about it.. someone else posted it... ;) share the wealth never
know when you'll need to borrow it

I don't find any pastespecial with data only option ...? However the
idea is to copy  only available data range  dynamically within the
specified bigger range

Wow this is good , manage to get it , but somehow because the range  that
I want to copy  got a link and formula that being hide , it still carry
the whole range , any ideas how to avoid this ?