named range

[Guest]

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.

 

[Don Guillett]

Tell us more

 

[Guest]

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

 

[Guest]

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

 

[Guest]

jhyatt,

I am a little confused about your question. You state your looking for any
occurance of the word "Promotion" but this code is naming a range "Promotion".

What do you want to have happen? If you want to look for the word
"promotion" then what do you want to do when it is found? Perhaps copy the
entire row of data or maybe selected cells and paste them in a sheet
named(??)?

 

[Guest]

I have a workbook that has a sheet for every day. on the sheets are the
people that have come into the office. some receive promotional prices i am
trying to find all entries so i can track the promotions. all entries are in
the same column. i want to find all and put them on a totals sheet in the
workbook.

 

[Guest]

jhyatt,

how many columns do you want for each person put into the summary sheet? If
the promotion is found what do we copy over for example,
column k = Promotion
column j=name
columni=time
columnh=date
columnl=price

I would only copy over the neccessary data.

 

[Guest]

the way the sheets is set.

column a = name
column b = date
column c-e = billed
column f = rate
coulmn g = discount price
coulumn k = discount type

the only info i would like to bring over would be the name, date, discount
price

 

[Guest]

jhyatt,

This should take of it. Make to put the Public variable declaration at the
top of a code module.

'*****************************
Public varValues(11) As Variant
'*****************************

Sub jhyatt()
Dim Oldsheet As String
Dim i As Integer
Dim MyVal As String
Dim ShtName As String 'Variable to simulate your code

ShtName = "Sheet4" '"Sheet4" should be 'Total' or 'Summary' in your code
Oldsheet = ActiveSheet.Name 'Capture sheet so we can return to it
MyVal = "Promotion" 'The value you are looking for
i = 0

Range("k1").Select 'The column you are searching
If ActiveCell <> "" Then
Do Until ActiveCell = ""
If ActiveCell = MyVal Then
varValues(11) = ActiveCell
varValues(1) = ActiveCell.Offset(0, -10)
varValues(2) = ActiveCell.Offset(0, -9)
varValues(3) = ActiveCell.Offset(0, -8)
varValues(4) = ActiveCell.Offset(0, -7)
varValues(5) = ActiveCell.Offset(0, -6)
varValues(6) = ActiveCell.Offset(0, -5)
varValues(7) = ActiveCell.Offset(0, -4)
varValues(8) = ActiveCell.Offset(0, -3)
varValues(9) = ActiveCell.Offset(0, -2)
varValues(10) = ActiveCell.Offset(0, -1)

i = i + 1
Call PasteMeHerejhyatt(Oldsheet, i, ShtName)
End If
ActiveCell.Offset(1, 0).Select
Loop

End If
End Sub

Function PasteMeHerejhyatt(shtOld As String, k As Integer, ShtName As String)

Sheets(ShtName).Select 'This will be the 'Total' or 'Summary' sheet
Sheets(ShtName).Range("A1").Select
ActiveCell.Offset(k, 0) = varValues(1)
ActiveCell.Offset(k, 1) = varValues(2)
ActiveCell.Offset(k, 2) = varValues(3)
ActiveCell.Offset(k, 3) = varValues(4)
ActiveCell.Offset(k, 4) = varValues(5)
ActiveCell.Offset(k, 5) = varValues(6)
ActiveCell.Offset(k, 6) = varValues(7)
ActiveCell.Offset(k, 7) = varValues(8)
ActiveCell.Offset(k, 8) = varValues(9)
ActiveCell.Offset(k, 9) = varValues(10)
ActiveCell.Offset(k, 10) = varValues(11)

'return to sheet that call came from and continue
Sheets(shtOld).Select
End Function