Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: "Move Values" button

  1. #1
    Guest

    Default

    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

  2. #2
    Guest

    Default

    Try this:

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

    HTH

    GaryB

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Guest

    Default

    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

  5. #5

    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  6. #6
    Guest

    Default

    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

  7. #7
    Guest

    Default

    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

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Guest

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •