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