Announcement

Collapse
No announcement yet.

Conditional Copy and Paste

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Conditional Copy and Paste



    Hello Everyone,

    I am having a problem with what I think should be an easy piece of VBA.

    Dim i As Variant
    Dim ws1 As Worksheet
    Dim wsindex1 As String
    Dim ws_lookup1 As Worksheet

    Set ws1 = ActiveSheet
    CurrentSheet = ws1.Name 'Updates Global Variable "CurrentSheet"

    wsindex1 = ws1.Index


    If wsindex1 > 1 Then

    Sheets(wsindex1 - 1).Select
    Set ws_lookup1 = ActiveSheet

    For i = 12 To 22

    [COLOR=#FF0000] If Worksheets(ActiveSheet).Cells(i, 5).Value = "Down" Then[/COLOR]

    Worksheets(ActiveSheet).Rows(i).Copy

    Worksheets(CurrentSheet).Cells(i, 1).Select

    Worksheets(CurrentSheet).Paste
    End If
    Next

    End If



    This is supposed to conditionally copy and paste from the previous sheet to the current sheet depending on what is in E12:E22 when you click a button (I am going to add more conditions once this problem is solved). New sheets get added to this workbook weekly and data is transferred twice a day (once a shift). This is why I need to use the ActiveSheet function and can't just have the name of the sheet.

    The text is red is where I'm getting a Mismatch error. I don't know why this is happening. Any help would be awesome


  • #2
    Try changing
    Code:
        For i = 12 To 22
    
     If Worksheets(ActiveSheet).Cells(i, 5).Value = "Down" Then
    
        Worksheets(ActiveSheet).Rows(i).Copy
    
        Worksheets(CurrentSheet).Cells(i, 1).Select
    
        Worksheets(CurrentSheet).Paste
        End If
        Next
    
    End If
    to
    Code:
    With ws_lookup1
        For i = 12 To 22
            If .Cells(i, 5) = "Down" Then
                .Rows(i).Copy Sheets(CurrentSheet).Cells(i, 1)
            End If
        Next
    End With
    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Comment


    • #3
      Yeah! That works. I am pretty new to VBA and excel in general. How come this doesn't need some kind of paste function?

      Comment


      • #4


        After copy method is used the destination for pasting can be immediately added to the same line of code, only if PasteSpecial is to be used does the destination need to be on a new line of code.
        We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

        Comment

        Working...
        X