VBA - Copying a defined cell range and pasting to another worksheet.

[DanielK]

Good Afternoon!

So I have no VBA background, but I have managed to put this together for a workbook where the ultimate goal is to have users (Engineers) select certain checkboxes. When they are done (And there will be a lot of checkboxes), they click the button "Build" and depending on their selections, they will generate a new worksheet or new workbook (Haven't decided on that one yet) with all the selected information pasted into it, in order.

I'm currently testing out the "Copying from defined range" part of this.

Sub Merge()
    
    Select Case Range("H3").Value
    
    Case 1
        If Range("H3") = 1 Then
    
            Sheets("Data").Select
            Range("A1:A8").Select
           Selection.Copy
    
            Sheets("UI").Select
            Cells(1).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
               False, Transpose:=False
        End If
    Case Else
        MsgBox "Case 1 - Failed."
    End Select
    
    Select Case Range("H6").Value
    
    Case 2
        If Range("H6") = 1 Then
        
            Sheets("Data").Select
            Range("A10:A17").Select
            Selection.Copy
    
            Sheets("UI").Select
            Cells(1).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
               False, Transpose:=False
        End If
    Case Else
        MsgBox "Case 2 - Failed."
    End Select
End Sub

Now when you click a checkbox, the cell it is linked to will say TRUE. So originally, i set the If statement to TRUE, but i just got a failure message. Then i tried "TRUE". Error again. So I made it point to another cell with the formula

=IF(F3=TRUE,1,"")

Now it works.
At least for Case 1.
Case 2 keeps giving me the "Case 2 - Failed." message.

I'm not sure why either of these are happening.

On another note, if Case 1 = False and Case 2 = True, would case 2 start at cell A1?


Here is a link to the file via my Google Drive
https://drive.google.com/file/d/0Bza853yM-QSMOXhsR0E2QXpWTnc/edit?usp=sharing

Thanks for your wisdom,
-Dan

 

[ChrisM]

I think you misunderstand how a Select Case statement works. There is no need for your If-Then test inside each Case block.

For example:

Select Case Range("H3").Value
 Case 1 'if H3 =  1 then
     a + b + 1
 Case 2 'if H3 = 2 then
     a + b + 2
 Case Else 'if H3 <> 1 or 2 then
    a + b
End Select

The test for H3 being equal to something occurs on the "Case 1" line.

So, take the If-Then out of your code.

Now looking at your second Select-Case block:

Your testing for H6 = 2 (because you used Case 2), but then right afterwards in your If-Then you are testing for H6 to be = 1

I think you want to test whether H6 is equal to 1, right? In that case it would be "Case 1".

Now to change gears a little bit, you only use Select-Case when you are choosing from a number of possible test values. In your case you are only looking for "1", so there is not even a reason to use Select-Case. Just use an If-Then-Else and be done with it.

Sub Merge()
        If Range("H3") = 1 Then
    
            Sheets("Data").Select
            Range("A1:A8").Select
           Selection.Copy
    
            Sheets("UI").Select
            Cells(1).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
               False, Transpose:=False
        Else
              MsgBox "Case 1 - Failed."
        End If
         ....
         ....

 

[DanielK]

Ah.
So Case 100 would mean i'm looking for the value of 100 by default then?
So that is why it always pushed the "Failed" message box when I tried the values of = "True" or = True.

The way you put it allowed me to nix the middle man of converting a TRUE/FALSE to 1/""

Now my code reads

Sub Merge()
       
    If Range("F3") = True Then
    
            Sheets("Data").Select
            Range("A1:A8").Select
           Selection.Copy
    
            Sheets("UI").Select
            Cells(1).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
               False, Transpose:=False
        Else
              MsgBox "Part 1 - Failed."
    End If
    
    If Range("F6") = True Then
        
            Sheets("Data").Select
            Range("A10:A17").Select
            Selection.Copy
    
            Sheets("UI").Select
            Cells(10).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
               False, Transpose:=False
        Else
            MsgBox "Part 2 - Failed"
    End If


End Sub

Uploaded the more updated version
https://drive.google.com/file/d/0Bza853yM-QSMakliS3J6U2x4YjA/edit?usp=sharing

So now i just have to figure out why it isn't converting pretty red and blue formatting when it copies it over. XD

I think my boss is going to be sending me to some "vb for excel" classes in the future...

Thanks for your help!
-Dan

 

[ChrisM]

You're not getting the red and blue formatting because you are copying and pasting values, no different than if you right click in the worksheet and choose paste special...Values, you will only get the numbers, nothing else.

You only get the formatting two ways:
1. A normal Copy and Paste (will work fine unless your source data is a formula and you DONT want to copy the formula)
2. A second operation to only paste the formats:

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
               False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:= _
               False, Transpose:=False

 

[DanielK]

Thanks again Chris!

I figured out the formats part but forgot to add in everything else after it.
I try to only post questions when i'm massively stuck on something.

Now i have to figure out the rest.
I learn more on these boards than the one VB class I took back in college, and forgot most of the info from it because what i learned didn't apply very much to my job. Like how to make a hangman game...

Thanks for the info!
=)
-Dan