this is the code i am using what i need to do is copy the range and paste to
another sheet. there may be an easier way to accomplish what i am trying to
do but this is the only way have found to find all records that contain
"promotion"

Public Sub AddNamepro(ByVal Promotion As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("k")
Set rngFound = rngToSearch.find(What:=Promotion, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
ThisWorkbook.Names.Add Promotion, rngFoundAll.Address
End If

"JRForm" wrote:

jhyatt,

Try
Selection.Copy 'copy your range
Sheets("Sheet2").Select 'go to the sheet you want
Range("A16").Select 'the area to place it
ActiveSheet.Paste 'paste

"jhyatt" wrote:

I have code that sets a named range automaticlly now i am trying copy that
range to anither sheet in the workbook but everthing i have tried fails.

any help would be appriciated thank you in advance.