S
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
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