"Move Values" button

[Guest]

I have tried for about 10 hours now to write a macro, whereby I can do the equivalent of a "cut special" and "paste special", so that I only end up moving values, and nothing else. Does anyone know where I can find such a macro already written? Or ideas on what I should do to write one? My inexperience with VBA is very frustrating!

Thank you
-Josh

 

[Guest]

Try this:

Selection.Copy
Range("A1").PasteSpecial Paste:=xlValues

HTH

GaryB

 

[Dave Hawley]

Hi

Afraid there is no such thing as a "Cut special values" but this will do a similar thing:

Dim strFormat As String
strFormat = Range("D20").NumberFormat
Range("D20") = Range("A1")
Range("D20").NumberFormat = strFormat


_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training
If it's Excel, then it's us!
This message was edited by Dave Hawley on 2002-03-11 09:15

 

[Guest]

I must not have done a good job explaining myself. What I need would be like a "move values" button. IOW:

1) Select a range by user input (say A1:C3 on sheet1)
2) Copy the range
3) Paste the range by user input (say B1:D3 on sheet2)
4) GO BACK AND CLEAR THE VALUES FROM THE RANGE IN STEP 1

All the user would do then, is 1) click a button, 2) select a range, 3) click ok, 4) select another range, 5) click ok.

The values would be gone from the "cut special" spot, and would be now located in the "paste special" spot. Obviously, this must all be done inside the same macro, or the orginal range would have to be cleared manually.

SURELY, someone has written a "move values" macro??!

Thanks
-Josh

 

[Legacy 948]

Try this ;-

Sub Cut_Paste()
Dim source As Range, dest As Range
r1:
On Error Resume Next
Application.DisplayAlerts = False
Set source = Application.InputBox("Enter or select a range", Type:=8)
Application.DisplayAlerts = True
On Error GoTo 0
If source Is Nothing Then
MsgBox "No range entered or selected"
End
ElseIf source.Areas.Count <> 1 Then
MsgBox "You must select one contiguous range only"
Set source = Nothing
GoTo r1
End If
r2:
On Error Resume Next
Application.DisplayAlerts = False
Set dest = Application.InputBox("Enter or select one cell", Type:=8)
Application.DisplayAlerts = True
On Error GoTo 0
If dest Is Nothing Then
MsgBox "No range entered or selected"
End
ElseIf dest.Cells.Count <> 1 Then
MsgBox "You must select one cell only"
Set dest = Nothing
GoTo r2
End If
source.Cut Destination:=dest
End Sub

 

[Guest]

Well, that is getting closer. Let me clarify; the problem is that I want to only move the cell contents, NOT the formatting. I think you can now fully see my delimma. I don't think you can use a "cut" at all. It seems to me that what you have to do is 1) copy, 2) paste special values, 3) clear the copied range of values. The macro would have to keep track of the orginal range so that it can "clean up" as the last step.

Thank you, very sincerely, for your effort.

-Josh

 

[Guest]

Wait a sec... Here it is! Thank you Arviragus. All I really had to do was change those last few lines of your code. Perhaps you erroneously assumed that I was smart enough to see this... Oh well!

Here it is with the change:

Sub Cut_Paste()
Dim source As Range, dest As Range
r1:
On Error Resume Next
Application.DisplayAlerts = False
Set source = Application.InputBox("Enter or select a range", Type:=8)
Application.DisplayAlerts = True
On Error GoTo 0
If source Is Nothing Then
MsgBox "No range entered or selected"
End
ElseIf source.Areas.Count <> 1 Then
MsgBox "You must select one contiguous range only"
Set source = Nothing
GoTo r1
End If
r2:
On Error Resume Next
Application.DisplayAlerts = False
Set dest = Application.InputBox("Enter or select one cell", Type:=8)
Application.DisplayAlerts = True
On Error GoTo 0
If dest Is Nothing Then
MsgBox "No range entered or selected"
End
ElseIf dest.Cells.Count <> 1 Then
MsgBox "You must select one cell only"
Set dest = Nothing
GoTo r2
End If
source.Copy
dest.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
source.ClearContents
End Sub

Thank all of you for your help

 

[Chris Davison]

On 2002-03-12 15:16, Anonymous wrote:
.....What I need would be like a "move values" button. IOW:

1) Select a range by user input (say A1:C3 on sheet1.........
Thanks
-Josh

Josh mate, what's "IOW" ?

ta
Chris

 

[Guest]

My apologies; that is short for "In Other Words". I use a lot of acronyms when corresponding with friends, so much so that I forget that they are not necessarily clear to other people.

-Josh