Glad I could help.

If you ever decide that you want to allow the user to pick the cells to copy,
you could tell them to select the range first and use that Selection--or just
ask for the range with a different application.inputbox.

wrote:

This is perfect Thank YOU!

As for your question, the reason I am always coping from B39:p39 is there is
a series of formulas there that gets the information they need for a report.
Again Thank you so very much Dave!

"Dave Peterson" wrote:

So you're always copying from B39:P39?

Dim RngToCopy as range
dim DestCell as range

with activesheet
set rngtocopy = .range("B39:p39")

set destcell = nothing
on error resume next
set destcell = application.inputbox(Prompt:="select a cell",type:=8).cells(1)
on error goto 0

if destcell is nothing then
exit sub 'user hit cancel
end if
End with

with destcell.resize(rngtocopy.rows.count,rngtocopy.col umns.count)
.value = rngtocopy.value
With .font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
end with
End With



wrote:

I am trying to get an input box that will tell what cell I want it to copy
paste information in. I have it to where it will pick the cell, but it won't
do the second half that I need it to do. I know it shouldn't be this hard,
but for me it is today.
Any help would be greatly appreciated.

'
' Update_Agents Macro
' Macro recorded 4/9/2007 by aemmi
'

'
Range("B39:P39").Select
Selection.Copy
Range("B27").Select <this is where I need to be able to change the cell
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
End Sub

--

Dave Peterson


--

Dave Peterson