Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy paste methods.....
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy paste methods.....
'Select your range then do this:
Selection.Copy 'then go to where you wish to paste and do this: ActiveSheet.Paste HTH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy paste methods.....
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 DTHIS web: www.excelusergroup.org web: www.nickhodge.co.uk "SteveDB1" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy paste methods.....
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. "Mike H." wrote: 'Select your range then do this: Selection.Copy 'then go to where you wish to paste and do this: ActiveSheet.Paste HTH |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy paste methods.....
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy paste methods.....
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? "Nick Hodge" wrote: 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 DTHIS web: www.excelusergroup.org web: www.nickhodge.co.uk "SteveDB1" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy paste methods.....
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? "Stuart Bray" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy paste methods.....
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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Automating copy/paste/paste special when row references change | Excel Programming | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming |