Add one or more columns while copying data data fr

2019-08-30 18:58发布

Below code is copying data from sheet1 to sheet2 based on criteria mentioned in below code. I want to insert columns in the beginning or at the end in sheet 2 when i run the VB code. It would be great if i can also provide different types of filters option in each cell for each new column that i would like to add. For example i would like to add one new column in sheet2 when VB code paste data from sheet1 to sheet2. Column name "Action" with filter option "Close, leave, open" for each cell in column "Action"

Option Explicit

Private Sub CommandButton21_Click()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim i As Long, MyUnion As Range, LRow As Long

For i = 2 To ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    If ws.Range("G" & i) > #10/31/2013# Or ws.Range("AA" & i) = "Investigate" Or ws.Range("AA" & i) = "Leave Open" Then
        If Not MyUnion Is Nothing Then
            Set MyUnion = Union(MyUnion, ws.Range("G" & i))
        Else
            Set MyUnion = ws.Range("G" & i)
        End If
    End If
Next i

If Not MyUnion Is Nothing Then
    With ThisWorkbook.Sheets("Sheet2")
        LRow = .Range("A" & .Rows.Count).End(xlUp).Offset(1).Row
        MyUnion.EntireRow.Copy .Range("A" & LRow)
    End With
End If

End Sub

0条回答
登录 后发表回答