After copying in Macro - come back to previous sheet

**JK1234** · 09-28-2008, 04:23 PM

I still need to look up how to use CASE properly,

but besides that, this is something I was trying to do. Of course it does not work yet? Suggestions??

**MatrixMan** · 09-29-2008, 08:55 AM

Here's how your case should look, but I don't understand what you mean by "for each selection" ... each selection of what?

    Select Case Range("N5")
        Case 1 To 5
            Set Ws = Worksheets("Sheet2")
        Case 2, 8, 14
            Set Rng2 = Range("C36:C41")
    End Select

    Select Case Range("Q5").Value
        Case 1
            Set Rng1 = Range("C28:C33")
    End Select

**royUK** · 09-29-2008, 10:05 AM

Not sure if this is what you want, sheet references might need changing, but I have attempted to change your code to use Select Case

Sub AutoShape1_Click()

    With Application
        .ScreenUpdating = False

        Dim Ws As Worksheet
        Dim Rng1 As Range
        Dim Rng2 As Range


        Select Case Range("N5").Value
            Case 1 To 5: Set Ws = Worksheet("Sheet2")
            Case 2, 8, 14: Set Rng2 = Range("C36:C41")
            Case Else: MsgBox "Incorrect entry"
        End Select

        Select Case Range("Q5").Value
            Case 1: Set Rng1 = Range("C28:C33")
            Case Else: MsgBox "Incorrect entry"
        End Select

        'The next part would do the copying and pasting depending upon what I selected

        Rng1.Copy Destination:=Rng2
        .ScreenUpdating = False
        .CutCopyMode = False
    End With
End Sub

**JK1234** · 09-29-2008, 10:41 AM

I think we are close.

the one line

HTML Code:

gives me an error
if I do this:

HTML Code:

It works with assigning a value to the rng2

**MatrixMan** · 09-29-2008, 11:05 AM

Rng1.Copy Destination:=Ws.Range("C28:C33")

Versus:

Rng1.Copy Destination:=Rng2

The 2nd one should be

Rng1.Copy Destination:=Ws.Rng2

... so it goes to the right sheet, otherwise you're cutting & pasting to the same place in the active sheet ..

Or - since you've got different variables for the same range address (as far as I can tell from the snippet) then set Rng2 explicitly such as:

Set Rng2=Ws.Range("C28:C33")

And then you can:

Rng1.Copy Destination:=Rng2

Hope that helps

**JK1234** · 09-29-2008, 11:11 AM

When I do change to this:

HTML Code:

I get the error: Method or data member not found. Is it assinged or DIm correctly??

I also thought that would work.

**MatrixMan** · 09-29-2008, 11:28 AM

Ah ha! I think it may be because Rng2 is only set if a particular case condition is met ... When stepping through, hover over Rng2 and if it says "Nothing", then that's your problem

**JK1234** · 09-29-2008, 09:56 AM

I was looking for more examples as how to fix my macro but I am still in need of more expertise.

I attached my last go around. I did manage to change another macro by using variables. Yeally shortens the code alot! So I am learning.