Controls Toolbox Combobox Problem

[larrycav]

I'm doing something wrong and can't figure it out.

I have several macros that copy and past data to 2 different worksheets. Recent changes require that I give the users a selection option for which worksheet the data gets copied to.

I have a control toolbox combobox as the reference selection in the VBA code to decide which worksheet the data should go to.

I have cell L55 as the linked cell.

I thought I would be able to refer to the value in L55 Linked cell as the reference for which sheet to copy the data to. When I F8 through the code the cursor jumps to the first IF statement, end if, then the second if, then to end if. It's not throwing an error though.

If (L55") =" data" Then
Range("b7:E31").Select
Selection.Copy
Sheets("Data_Sheet").Select
Range("a7").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("sheet1").Select
Range("j2").Select
Application.ScreenUpdating = True
MsgBox "Data Copied to data_sheet"
End If
If ("L55") = "compare" Then
Range("b7:B31,e7:e31").Select
Selection.Copy
Sheets("CFM_Compare").Select
Range("a4").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("sheet1").Select
Range("j2").Select
Application.ScreenUpdating = True
MsgBox "Data Copied Compare Sheet"
End sub

 

[jsotola]

your if statements are the problem

the first contains a typo even

you are comparing two text strings "L55" = "compare" (these will never be equal)

you want to compare the contents of cell L55 .... Range("L55") = "compare"

here is your code with corrections ( all select statements have been removed because they are not needed)

you also need to put in the correct sheet name in "fill_in_sheet_name"

Sub test()


    Application.CutCopyMode = False
    
    If Sheets("fill_in_sheet_name").Range("L55") = " data" Then
        
        Sheets("Data_Sheet").Range("a7").Value = Sheets("fill_in_sheet_name").Range("b7:E31").Value
        MsgBox "Data Copied to data_sheet"
    
    End If
    
    If Sheets("fill_in_sheet_name").Range("L55") = "compare" Then
        
        Sheets("CFM_Compare").Range("a4").Value = Sheets("fill_in_sheet_name").Range("b7:B31,e7:e31").Value
        MsgBox "Data Copied Compare Sheet"
    
    End If
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True


End Sub

 

[AlphaFrog]

Try this

    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] L[COLOR=darkblue]Case[/COLOR](Range("L55").Value)
        [COLOR=darkblue]Case[/COLOR] "data"
            Range("B7:E31").Copy
            Sheets("Data_Sheet").Range("A7").PasteSpecial Paste:=xlPasteValues
        Case "compare"
            Range("B7:B31,E7:E31").Copy
            Sheets("CFM_Compare").Range("A4").PasteSpecial Paste:=xlPasteValues
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
    Range("J2").Select
    MsgBox "Data Copied to " & Range("L55").Value & " Sheet"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[larrycav]

I thank both of you. I can't believe the silly mistakes I had in my code. With both examples I learned some tips for shorter code. This forum is always so helpful

 

[larrycav]

Try this

    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] L[COLOR=darkblue]Case[/COLOR](Range("L55").Value)
        [COLOR=darkblue]Case[/COLOR] "data"
            Range("B7:E31").Copy
            Sheets("Data_Sheet").Range("A7").PasteSpecial Paste:=xlPasteValues
        Case "compare"
            Range("B7:B31,E7:E31").Copy
            Sheets("CFM_Compare").Range("A4").PasteSpecial Paste:=xlPasteValues
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
    Range("J2").Select
    MsgBox "Data Copied to " & Range("L55").Value & " Sheet"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

I decided on this code & have it working, plus added to it. I have another question. Using the select case method, how can I handle this situation?

I have 3 option buttons, linked to "a47" on the source worksheet. Those buttons are selected for the next round of copying when the case "compare" is true.
They will be used to point the paste operation on Compare worksheet to the second and third cell ranges. Can I use "and" in a "case" statement or do I have to back to If statements?

 

[larrycav]

I don't know if I stated my goad clearly in the above post so I'll try to clarify.

What I'm after is satisfying multiple conditions that determine :
a - which worksheet to copy the data to [the current code does that just fine]
b - if CASE "compare" is true, then look at which option button is selected by referring to the value of "A47", then copy the values to a range of cells on the "CFM_compare" worksheet. The value of "A47" will determine the location to copy to. It will have 3 possible locations based on "A47" having a value of 1, 2 or 3. [per which option button is selected]

Effectively during the first copy, 2 ranges of cells get copied, then on the second copy, only 1 range of cells gets copied and pasted next to the first, same scenario for the third copy...pasted next to the second.

 

[larrycav]

Never mind... I solved it by adding some IF statements. But if you looked to help...Thank You!

 

[larrycav]

In case this helps someone down the road, here's the code I ended up with that does the job.

Sub Port2_Intake()
Select Case LCase(Range("L55").Value)
        Case "data"
            Range("B7:E31").Copy
            Sheets("Data_Sheet").Range("A7").PasteSpecial Paste:=xlPasteValues
        Case "cfm_compare"
        If Range("a47").Value = 1 Then
            Range("B7:B31,E7:E31").Copy
            Sheets("CFM_Compare").Range("f4").PasteSpecial Paste:=xlPasteValues
            Sheets("CFM_Compare").Range("f3").Value = Range("b6")
            Sheets("CFM_Compare").Range("g3").Value = "CFM1"
            Sheets("CFM_Compare").Range("f2").Value = "Port2Intake"
            End If
        If Range("a47").Value = 2 Then
            Range("E7:E31").Copy
            Sheets("CFM_Compare").Range("h4").PasteSpecial Paste:=xlPasteValues
            Sheets("CFM_Compare").Range("h3").Value = "CFM2"
            End If
         If Range("a47").Value = 3 Then
            Range("E7:E31").Copy
            Sheets("CFM_Compare").Range("i4").PasteSpecial Paste:=xlPasteValues
            Sheets("CFM_Compare").Range("i3").Value = "CFM3"
            End If
    End Select
    Application.CutCopyMode = False
    Range("J2").Select
    MsgBox "Data Copied to " & Range("L55").Value & " Sheet"
End Sub