copy paste methods.....

[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.

 

[Mike H.]

'Select your range then do this:
Selection.Copy

'then go to where you wish to paste and do this:
ActiveSheet.Paste

HTH

 

[Nick Hodge]

Steve

Just do it in one go.

Present your two inputboxes one after the other to collect the ranges and
then do AFTER

MyRng1.Copy Destination:=MyRng2

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[SteveDB1]

Mike,
Thanks for the quick response.

The error that I get is a 1004, "Paste Method of worksheet class failed"

as mentioned-- I've tried a number of different types of pasting, and none
of them are working. Which is weird, because they've worked in the past with
other macros.

 

[Stuart Bray]

If you aren't bothered about formats, then don't bother with paste or
pastespecial. These methods can be unstable when copying objects, and
they make the sheets be selected.

Instead you can just set the values from one range to equal the values
of another range, like this:

myRng2.value = myRng1.value

 

[SteveDB1]

Nick,

I like that, but my cells are merged, and it says that it cannot changed
part of a merged cell.

How can I bypass, or not affect the merged cells?

 

[SteveDB1]

Now that's an interesting way to go about it.... I like that.... I'll
definitely keep it handy for future uses.
While it does indeed copy my contents, it removes the merging of my source
cell group; thus forcing me to go back and remerge them. And when I try to do
it by vba, using the
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Merged = True
End With
it rejects the (both/either) : .merged/merge = true.
Got any thing else that works?

 

[SteveDB1]

Ok,
seeing that none of the fixes offered thus far have succeeded, I'd like to
ask the following. And yes, I've tried them all-- two or three different
ways. I've gotten 438 errors, 1004 errors, and Stuart's version resulted in
my cells to unmerge. And this was why I wanted to create the macro to begin
with-- so I could copy, and prevent all the properties of a cell group of
coming over to the destination.

With other macros that I've made in which I used the range.select,
selection.copy, activesheet.paste, they've worked as written.

So it seems to me that it's got to be something preceeding what I've used.

The only thing that I can think of that might cause problems is my use of the

application.inputbox().cells(1), or application.inputbox().areas(1)

I've tried both, and neither works with the activesheet.paste. Thus leaving
me thinkingthat it was my .paste use, and now it's got me wondering if the
application.inputbox is the cause.


Admittedly, none of my previous macros using the .copy, and .paste had
interactive input options. So.... what have I missed here that is causing the
activesheet.paste to fail?

Again, thank you for your helps.