Autofilter based on user inputbox

[Steve]

Hi everyone,

I have some code below that copies rows from one sheet to another based on an autofilter criteria in column K. Is there as way for the user to selectthe column that he wishes to autofilter off of as opposed to having it hardcoded to column K? Thank you!

Sub Copy_Rows()

Dim wksSource As Worksheet, wksTarget As Worksheet


blCancelled = False

On Error Resume Next
Set wksSource = ActiveSheet
UserForm1.Show '//get wksTarget sheetname
Set wksTarget = Sheets(gsWksTargetName)
On Error GoTo 0

If blCancelled Then Exit Sub

wksSource.Select
Application.ScreenUpdating = False
With wksTarget
.Rows("1:" & CStr(.UsedRange.Rows.Count)).ClearContents
.Rows("1:" & CStr(.UsedRange.Rows.Count)).ClearComments
.Rows("1:" & CStr(.UsedRange.Rows.Count)).Interior.ColorIndex = xlNone
End With

'***********************************************************
'UsedRange work properly with hidden columns??
'***********************************************************

With wksSource
.Columns("K:K").AutoFilter Field:=1, Criteria1:="Y"
.UsedRange.Copy
wksTarget.Range("1:1").PasteSpecial Paste:=xlPasteColumnWidths
.UsedRange.Copy wksTarget.Range("1:1") '//put the data
.Columns("K:K").AutoFilter
End With
Application.ScreenUpdating = True

End Sub

 

[GS]

Steve,
I've inserted my replies below in your posted code...

Sub Copy_Rows()

Dim wksSource As Worksheet, wksTarget As Worksheet


blCancelled = False

On Error Resume Next
Set wksSource = ActiveSheet
UserForm1.Show '//get wksTarget sheetname
Set wksTarget = Sheets(gsWksTargetName)
On Error GoTo 0

If blCancelled Then Exit Sub

***What is blCancelled? It appears to be used here as a global
variable, and if so I'd suggest you indicate that as follows:

Public gbCancelled As Boolean
OR
Public g_bCancelled As Boolean

...so it's apparent that it's a global variable (1st prefix ="g") and
its type is Boolean (2nd prefix ="b"). In similar fashion, you might
define a module-level variable as:

Dim mbCancelled As Boolean
OR
Dim m_bCancelled As Boolean
***Since this is the active sheet, why select?

wksSource.Select

Application.ScreenUpdating = False

'***Since you're working on all cells containing data...
With wksTarget.Cells
.ClearContents
.ClearComments
.Interior.ColorIndex = xlNone

End With

'***********************************************************
'UsedRange work properly with hidden columns??
'***********************************************************

Dim RngToFilter As Range
On Error Resume Next
Set RngToFilter = _
Application.InputBox(prompt:="Select the column to filter",
Type:=8)
On Error GoTo 0
If Not RngToFilter Is Nothing Then
If Application.WorksheetFunction.CountA(RngToFilter.EntireColumn) _

0 Then

With

With wksSource
.RngToFilter.EntireColumn.AutoFilter Field:=1, Criteria1:="Y"

 

[GS]

Sorry, ..there's a typo!

Remove "With" below the line...

If Application.WorksheetFunction.CountA(RngToFilter.EntireColumn) _

 

[GS]

Also, I forgot to backspace out the dot when I pasted
'RngToFilter.EntireColumn' in your 'With wksSource' construct. So these
2 lines should read...

RngToFilter.EntireColumn.AutoFilter Field:=1, Criteria1:="Y"
and...
RngToFilter.EntireColumn.AutoFilter