S
SteveDB1
morning all.
I've made a macro that will allow me to pick a range of my choosing--
through an input box, and then place it where I want.
The problem that I'm having is that it doesn't like of of my .paste methods.
I've tried:
ActiveSheet.paste
ActiveCell.paste
Selection.paste
Range(myRng2).paste
myRng2.paste
Selection(myRng2).paste
and as you can see below, I presently have a
with selection
.paste
end with
And since I've just picked this up after setting it aside for two weeks, I
could tried other variations, and have just forgotten about them. However,
the point is that nothing I've tried has worked.
Here is the macro, perhaps one of you can show me my error, and a fix.
Sub copyNmDwn()
Dim myRng1 As Range
Dim myRng2 As Range
Set myRng1 = Nothing
On Error Resume Next
Set myRng1 = Application.InputBox(Prompt:="Select cell range you wish to
copy from", Type:=8).Areas(1)
On Error GoTo 0
If myRng1 Is Nothing Then
Exit Sub 'user hit cancel.
End If
myRng1.Select
Selection.Copy
Set myRng2 = Nothing
On Error Resume Next
Set myRng2 = Application.InputBox(Prompt:="Select cell or range you want it
pasted to.", Type:=8).Areas(1)
On Error GoTo 0
If myRng2 Is Nothing Then
Exit Sub 'user hit cancel.
End If
myRng2.Select
With Selection
.Paste
End With
Application.ScreenUpdating = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
'set right border when at dual vertical lines.
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'set bottom border if at page boundary
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'set top border if at page boundary
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'set Left border for sake of it all....
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Application.ScreenUpdating = True
End Sub
Your helps are immensely appreciated.
I've made a macro that will allow me to pick a range of my choosing--
through an input box, and then place it where I want.
The problem that I'm having is that it doesn't like of of my .paste methods.
I've tried:
ActiveSheet.paste
ActiveCell.paste
Selection.paste
Range(myRng2).paste
myRng2.paste
Selection(myRng2).paste
and as you can see below, I presently have a
with selection
.paste
end with
And since I've just picked this up after setting it aside for two weeks, I
could tried other variations, and have just forgotten about them. However,
the point is that nothing I've tried has worked.
Here is the macro, perhaps one of you can show me my error, and a fix.
Sub copyNmDwn()
Dim myRng1 As Range
Dim myRng2 As Range
Set myRng1 = Nothing
On Error Resume Next
Set myRng1 = Application.InputBox(Prompt:="Select cell range you wish to
copy from", Type:=8).Areas(1)
On Error GoTo 0
If myRng1 Is Nothing Then
Exit Sub 'user hit cancel.
End If
myRng1.Select
Selection.Copy
Set myRng2 = Nothing
On Error Resume Next
Set myRng2 = Application.InputBox(Prompt:="Select cell or range you want it
pasted to.", Type:=8).Areas(1)
On Error GoTo 0
If myRng2 Is Nothing Then
Exit Sub 'user hit cancel.
End If
myRng2.Select
With Selection
.Paste
End With
Application.ScreenUpdating = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
'set right border when at dual vertical lines.
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'set bottom border if at page boundary
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'set top border if at page boundary
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'set Left border for sake of it all....
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Application.ScreenUpdating = True
End Sub
Your helps are immensely appreciated.