How to use If..Then properly?

Question

I'm the newest girl using VBA for Excel, so I've been doing a lot of research but since this isn't my knowledge field I'm pretty sure I'm missing something. I used an UserForm for selecting 5 different columns of data, if you choose option 1, you get data from "x" column, if you choose option 2, you get data from "y" column and so on. This data has to be copied and pasted from one worksheet to another, but I can't make it work.
Can you tell me what am I doing wrong?

Oh yeah! As a background, this isn't mine. The workbook was sent to me, and I have to edit it according to the needs of my workplace.

This is my code.

Private Sub UserForm_Click()
    If UserForm6.OptionButton1.Value = True Then
        Sheets("Operations").Select
        Range("B7:M33").Select
        Selection.ClearContents
        Sheets("Processes").Select
        Range("D2:D33").Select
        Selection.Copy
        Sheets("Operations").Select
        Range("B7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    ElseIf UserForm6.OptionButton2.Value = True Then

        Sheets("Operations").Select
        Range("B7:M33").Select
        Selection.ClearContents
        Sheets("Processes").Select
        Range("I2:I33").Select
        Selection.Copy
        Sheets("Operations").Select
        Range("B7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    ElseIf UserForm6.OptionButton3.Value = True Then

        Sheets("Operations").Select
        Range("B7:M33").Select
        Selection.ClearContents
        Sheets("Processes").Select
        Range("N2:N33").Select
        Selection.Copy
        Sheets("Operations").Select
        Range("B7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    ElseIf UserForm6.OptionButton4.Value = True Then

        Sheets("Operations").Select
        Range("B7:M33").Select
        Selection.ClearContents
        Sheets("Processes").Select
        Range("S2:S33").Select
        Selection.Copy
        Sheets("Operations").Select
        Range("B7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    ElseIf UserForm6.OptionButton5.Value = True Then

        Sheets("Operations").Select
        Range("B7:M33").Select
        Selection.ClearContents
        Sheets("Processes").Select
        Range("X2:X33").Select
        Selection.Copy
        Sheets("Operations").Select
        Range("B7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    End If
End Sub

I'd love any help you can give! And by the way, English isn't my native language so I hope you can understand most of what I'm asking.

Thank you!

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Sep 23 '19 at 15:33
Your code looks A-OK to me. I see no problem with any of it. Perhaps you could better explain exactly what you want it to do and what it's doing incorrectly now so we can help get you closer to working code. — JNevill, Sep 23 '19 at 15:37
@LilianaMartinez Please can you tell me more about the issue you are facing? (Your code looks Nice to me) — TourEiffel, Sep 23 '19 at 15:48
@Dorian ,Like I said, this draft was sent to me, and only had the data for one of our five bussiness units. The original macro (i used F8) copies the data from "Processes" to paste it on "Operations". I added the data for the other four bussiness units on "Processes", added the UserForm to select which b.u. data is needed and used copy-paste for selecting the right column. This is pretty hard to explain since there are other operationes involved, but I need this data to be in the right place for being showed to another userform that eventually leads to the document I need am I clear enoguh? — Liliana Martinez, Sep 23 '19 at 21:26
@Dorian Once again, I'm nothing but a total ignorant on the matter but I think the other macros involved doesn´t really affect this one.. — Liliana Martinez, Sep 23 '19 at 21:33
@LilianaMartinez So Should be the data ? Can you please provide a screenshot of what you have now and what you except to have ? — TourEiffel, Sep 23 '19 at 21:37
@Dorian I got tired of trying so, instead of using an UserForm, I used five Command Buttons and it worked as I wanted! I still don't know what I was doing wrong. Anyway, I appreciate your help a lot :) I used the code you wrote yesterday and just did the needed changes. Thank you!!! — Liliana Martinez, Sep 24 '19 at 19:34

score 2 · Answer 1 · answered Sep 23 '19 at 15:57

We can avoid the clipboard by setting the values directly and by using a range object we can rewrite a little and avoid the duplicate code:

Private Sub UserForm_Click()
    Dim rng As Range

    With Worksheets("Processes")
        Select Case True
            Case Me.OptionButton1.Value
                Set rng = .Range("D2:D33")
            Case Me.OptionButton2.Value
                Set rng = .Range("I2:M33")
            Case Me.OptionButton3.Value
                Set rng = .Range("N2:N33")
            Case Me.OptionButton4.Value
                Set rng = Range("S2:S33")
            Case Me.OptionButton5.Value
                Set rng = .Range("X2:X33")
        End Select
    End With
    With Worksheets("Operations")
        .Range("B7:M33").ClearContents
        .Range("B7").Resize(rng.Rows.Count).Value = rng.Value
    End With
End Sub

TourEiffel · Accepted Answer · 2019-09-23T15:54:54.767

1

Your code without Select would be more readable and also improve performances:

Private Sub UserForm_Click()
    If Me.OptionButton1.Value = True Then
        Sheets("Operations").Range("B7:M33").ClearContents
        Sheets("Processes").Range("D2:D33").Copy
        Sheets("Operations").Range("B7").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks:=False, Transpose:=False

    ElseIf Me.OptionButton2.Value = True Then

        Sheets("Operations").Range("B7:M33").ClearContents
        Sheets("Processes").Range("I2:I33").Copy
        Sheets("Operations").Range("B7").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks:=False, Transpose:=False

    ElseIf Me.OptionButton3.Value = True Then

        Sheets("Operations").Range("B7:M33").ClearContents
        Sheets("Processes").Range("N2:N33").Copy
        Sheets("Operations").Range("B7").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks:=False, Transpose:=False

    ElseIf Me.OptionButton4.Value = True Then

        Sheets("Operations").Range("B7:M33").ClearContents
        Sheets("Processes").Range("S2:S33").Copy
        Sheets("Operations").Range("B7").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks:=False, Transpose:=False

    ElseIf Me.OptionButton5.Value = True Then

        Sheets("Operations").Range("B7:M33").ClearContents
        Sheets("Processes").Range("X2:X33").Copy
        Sheets("Operations").Range("B7").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, SkipBlanks:=False, Transpose:=False

    End If
End Sub

edited Sep 23 '19 at 15:54

answered Sep 23 '19 at 15:41

TourEiffel

4,034
2
16
45

My only problem with this is the splitting of arguments across multiple lines - the macro recorder loves to do that. `SkipBlanks` argument name on one line, and the corresponding `:=` and the argument value on another... splitting named arguments on multiple lines should be criminal! – Mathieu Guindon Sep 23 '19 at 15:44
@MathieuGuindon yep Deleted this, But i think that this will not solve her issue, her code looks nice to me – TourEiffel Sep 23 '19 at 15:46
Agree. I think OP is missing a meaningful description for "doesn't work". Could be the active workbook isn't the one the code is assuming. Looks like it's code-behind for a `UserForm` - it being in the form's `Click` handler strikes me as odd, and then the code-behind referring to the form's default instance can't be good either (assuming the form is `UserForm6`), should be `Me`, not `UserForm6`. – Mathieu Guindon Sep 23 '19 at 15:50
@MathieuGuindon Should I edit and replace `UserForm6.OptionButton5` by `Me!OptionButton5` (For example)so she got a clean code – TourEiffel Sep 23 '19 at 15:52
1

Yes, but without the `!` bang operator - that would be derefencing the control from the `Controls` collection (i.e. `Me.Controls("OptionButton5")`), which is redundant since the control is already accessible directly with `Me.OptionButton5`. – Mathieu Guindon Sep 23 '19 at 15:53

How to use If..Then properly?

2 Answers2