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

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

Application.Inputbox may be what you are looking for. Change the variable
rngSource to the appropriate worksheet and watch for the word wrap the that
will undoubtedly occur when I post this macro to the newsgroup.


Sub test()
Dim rngSource As Range
Dim rngDest As Range

Set rngSource = Worksheets("Sheet1").Range("B39:P39") '<<CHANGE

On Error Resume Next
Set rngDest = Application.InputBox("Select Destination", Type:=8)
On Error GoTo 0

If rngDest Is Nothing Then Exit Sub

rngSource.Copy
With rngDest.Cells(1).Resize(rngSource.Rows.Count, rngSource.Columns.Count)
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
skipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
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
End Sub



" 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

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

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