Select sheet inside or outside other select

**MadRue** · 05-27-2007, 06:31 PM

This seems to be quite a unique problem I have, maybe it is a simple problem that no one else has.

I am trying to copy a value from one sheet and paste it to another sheet but I can not figure out where to put the Sheets("OrderData").Select.
If I remove it completely then it copies the values to current sheet, OrderForm, because Sheets("OrderForm").Select was used previously.
As soon as I try and place the OrderData.select in it doesnt work.

here is a snippet of my code.

Sheets("OrderData").Select

    If Range("A2") = "" Then
        ' if it's the first record to be added simply select a2
        Range("A2").Select
    Else
        'if it's not the first record position to insertion point
        Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Range("A1").Select
    End If

    ActiveCell = Date

    Sheets("OrderForm").Select

    If Range("C17").Value = Range("C18").Value Or Range("C17").Value = Range("C19").Value Or Range("C18").Value = Range("C19").Value Then
    MsgBox "You can not enter duplicate wine cases, please ensure you make unique selections and try again"
    
        Exit Sub

    End If

    For loopcount = 1 To 3
    
    Sheets("OrderForm").Range("C16").Offset(loopcount, 0).Range("A1").Copy
    Sheets("Price&GiftInfo").Range("RWM").PasteSpecial Paste:=xlValues
    Sheets("OrderForm").Range("E16").Offset(loopcount, 0).Range("A1").Copy
    
 '   Sheets("OrderData").Select

   Select Case Range("RWM")
    
        Case "Red"

            ActiveCell.Offset(0, 1).Range("A1").PasteSpecial Paste:=xlValues
            ActiveCell.Offset(0, -1).Range("A1").Select
        
        Case "White"
        
            ActiveCell.Offset(0, 2).Range("A1").PasteSpecial Paste:=xlValues
            ActiveCell.Offset(0, -2).Range("A1").Select
        
        Case "Mixed"
        
            ActiveCell.Offset(0, 3).Range("A1").PasteSpecial Paste:=xlValues
            ActiveCell.Offset(0, -3).Range("A1").Select
    End Select

    Next loopcount

I have the Sheets("OrderData").Select currently commented out where I assumed it would usually go.
Hopefully I have covered everything, I am a bit tired after spending many hours trying this so please excuse my noobishness.

**mikerickson** · 05-27-2007, 07:13 PM

Selecting and Active Cell are ususaly not needed. Also copy/PasteValues is dome much quicker when done with direct syntax

destinationRange.Value=sourceRange.Value

I modifyed your code to reflect this.

Dim destination As Range
Dim testValue As Variant
With Sheets("OrderData")
    If .Range("A2") = "" Then
        Set destination = .Range("A2")
    Else
       Set destination = .Range("a1").End(xlDown).Offset(1, 0)
    End If
    destination = Date
End With

With Sheets("OrderForm")
    If .Range("C17").Value = .Range("C18").Value Or .Range("C17").Value = .Range("C19").Value _
        Or .Range("C18").Value = .Range("C19").Value Then
        MsgBox "You can not enter duplicate wine cases, please ensure you make unique selections and try again"
        Exit Sub
    End If

    For loopcount = 1 To 3

        Sheets("Price&GiftInfo").Range("RWM").Value = .Range("C16").Offset(loopcount, 0).Value
        
        Select Case .Range("RWM")
            Case "Red"
                destination.Offset(0, 1) = .Range("E16").Offset(loopcount, 0)
             Case "White"
                destination.Offset(0, 2) = .Range("E16").Offset(loopcount, 0)
            Case "Mixed"
                destination.Offset(0, 3) = .Range("E16").Offset(loopcount, 0)
        End Select

    Next loopcount
End With

Also, if there is something in Sheets("OrderData").Range("A1"), testing for A2 is not needed.

Sheets("OrderData").Range("A1").End(xlDown).Offset(1,0)

will find the destination even if A2 is empty. If A1 isn't empty.

Select sheet inside or outside other select

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Select sheet inside or outside other select

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions