Hello Macro Veterans,
I am trying to write a macro that filters a worksheet, and then copies over certain columns to another worksheet in the same workbook. I want to assign variables because new columns are frequently added, changing the coordinates of other column titles. As of now, I have the variables working for the filtering part, but I am not sure how to create/apply variables to the part of the code that grabs the column and pastes it into another worksheet. I have placed dotted lines around the part of the code that I am having trouble with. It is currently hard-coded with the U2:U, but variables are necessary for flexibility.
The worksheet i want to copy from is "MOD"
The worksheet i want to copy to is "NO WELCOME OR 30MDL"
Hope someone can help, thank you.
Nick
Sub Prac2()
Dim i As Integer, rngData As Range
Set rngData = Range("A1").CurrentRegion
lrow = .Range("A" & .Rows.Count).End(xlUp).Row
With Worksheets("MOD")
i = Application.WorksheetFunction.Match("MGR_NAME", Range("A1:ZZ1"), 0)
rngData.AutoFilter Field:=i, Criteria1:="P, STEVEN", Operator:=xlOr, Criteria2:="M, LUCINDA"
i2 = Application.WorksheetFunction.Match("7060", Range("A1:ZZ1"), 0)
rngData.AutoFilter Field:=i2, Criteria1:="=", Operator:=xlOr, Criteria2:="="
End With
---------------------------------------------------------------------------
With Worksheets("MOD")
If Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
.Range("U2:U" & lrow).SpecialCells(xlCellTypeVisible).Copy _
Sheets("NO WELCOME OR 30MDL").Cells(Sheets("NO WELCOME OR 30MDL").Cells(Rows.Count, "A").End(xlUp).Row + 1, "A")
End If
End With
----------------------------------------------------------------------------
Sheets("MOD").Select
End Sub
Bookmarks