Re-set autofilter selection after adding a new row - programmatica
 

I have a worksheet that has an autofilter on a column called Assigned DFE.
When a user uses this filter they will just see the rows that pertain to
them. I created a command button that allows them copy one of their existing
rows to the next available row, but when this happens they must re-select
their name in the Assigned DFE column in order to see the new row that was
added. How can I get my command button to automatically show the new row
that was added without them having to do that extra step?

Thank you,

Sarah

Re-set autofilter selection after adding a new row - programmatica
 

After the commandbutton code copies the row, it should remove the autofilter
or showAll and reapply it with the users name as the criteria.

--
Regards,
Tom Ogilvy


"Sarah" wrote:

I have a worksheet that has an autofilter on a column called Assigned DFE.
When a user uses this filter they will just see the rows that pertain to
them. I created a command button that allows them copy one of their existing
rows to the next available row, but when this happens they must re-select
their name in the Assigned DFE column in order to see the new row that was
added. How can I get my command button to automatically show the new row
that was added without them having to do that extra step?

Thank you,

Sarah

Re-set autofilter selection after adding a new row - programma
 

That sounds like it'ss work. Do you know the command for finding out what
was selected for a filter?

"Tom Ogilvy" wrote:

After the commandbutton code copies the row, it should remove the autofilter
or showAll and reapply it with the users name as the criteria.

--
Regards,
Tom Ogilvy


"Sarah" wrote:

I have a worksheet that has an autofilter on a column called Assigned DFE.
When a user uses this filter they will just see the rows that pertain to
them. I created a command button that allows them copy one of their existing
rows to the next available row, but when this happens they must re-select
their name in the Assigned DFE column in order to see the new row that was
added. How can I get my command button to automatically show the new row
that was added without them having to do that extra step?

Thank you,

Sarah

Re-set autofilter selection after adding a new row - programma
 

If the user has selected a cell on a visible row in the autofilter and
presses the command button, it will copy that row to the bottom of the filter
range and reapply the filter to include that row and the existing criteria.

Private Sub Commandbutton1_Click()
Dim flt As Filter
Dim sCrit1() As String, sCrit2() As String
Dim sOp() As Long, rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim i As Long, cnt As Long
Dim rng4 As Range
If ActiveSheet.AutoFilterMode Then
Set rng = ActiveSheet.AutoFilter.Range
Set rng4 = Intersect(rng, ActiveCell)
If ActiveCell.Row = rng.Row Or _
rng4 Is Nothing Or _
ActiveCell.EntireRow.Hidden Then
MsgBox "Select a visible row to reproduce" & _
"in the filtered data"
Exit Sub
End If
i = 0
cnt = ActiveSheet.AutoFilter.Filters.Count
ReDim sCrit1(1 To cnt)
ReDim sCrit2(1 To cnt)
ReDim sOp(1 To cnt)
For Each flt In ActiveSheet.AutoFilter.Filters
i = i + 1
If flt.On Then
sCrit1(i) = flt.Criteria1
sCrit2(i) = ""
sOp(i) = 2
On Error Resume Next
sCrit2(i) = flt.Criteria2
sOp(i) = flt.Operator
On Error GoTo 0
End If
Next
ActiveSheet.ShowAllData
Set rng1 = rng.Columns(1).Cells
Set rng2 = rng(rng.Count + 1)
Set rng3 = Intersect(rng, ActiveCell.EntireRow)
rng3.Copy rng2
Set rng = rng.Resize(rng.Rows.Count + 1)
ActiveSheet.AutoFilterMode = False
rng.AutoFilter
For i = 1 To cnt
If sCrit1(i) < "" Then
If sCrit2(i) < "" Then
rng.AutoFilter Field:=i, Criteria1:=sCrit1(i), _
Operator:=sOp(i), _
Criteria2:=sCrit2(i)
Else
rng.AutoFilter Field:=i, Criteria1:=sCrit1(i)
End If
End If
Next
End If
End Sub

--
Regards,
Tom Ogilvy


"Sarah" wrote:

That sounds like it'ss work. Do you know the command for finding out what
was selected for a filter?

"Tom Ogilvy" wrote:

After the commandbutton code copies the row, it should remove the autofilter
or showAll and reapply it with the users name as the criteria.

--
Regards,
Tom Ogilvy


"Sarah" wrote:

I have a worksheet that has an autofilter on a column called Assigned DFE.
When a user uses this filter they will just see the rows that pertain to
them. I created a command button that allows them copy one of their existing
rows to the next available row, but when this happens they must re-select
their name in the Assigned DFE column in order to see the new row that was
added. How can I get my command button to automatically show the new row
that was added without them having to do that extra step?

Thank you,

Sarah

Re-set autofilter selection after adding a new row - programma
 

That was a bit more than I was looking for. I'm not that good with coding.
So there isn't a command that tells me what was selected for an autofilter?

For example I have a column called DFE. i've applied the autfilter to it and
now when the user makes a selection, I would like to know where this
selection is stored so that I can access it in the code.


"Tom Ogilvy" wrote:

If the user has selected a cell on a visible row in the autofilter and
presses the command button, it will copy that row to the bottom of the filter
range and reapply the filter to include that row and the existing criteria.

Private Sub Commandbutton1_Click()
Dim flt As Filter
Dim sCrit1() As String, sCrit2() As String
Dim sOp() As Long, rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim i As Long, cnt As Long
Dim rng4 As Range
If ActiveSheet.AutoFilterMode Then
Set rng = ActiveSheet.AutoFilter.Range
Set rng4 = Intersect(rng, ActiveCell)
If ActiveCell.Row = rng.Row Or _
rng4 Is Nothing Or _
ActiveCell.EntireRow.Hidden Then
MsgBox "Select a visible row to reproduce" & _
"in the filtered data"
Exit Sub
End If
i = 0
cnt = ActiveSheet.AutoFilter.Filters.Count
ReDim sCrit1(1 To cnt)
ReDim sCrit2(1 To cnt)
ReDim sOp(1 To cnt)
For Each flt In ActiveSheet.AutoFilter.Filters
i = i + 1
If flt.On Then
sCrit1(i) = flt.Criteria1
sCrit2(i) = ""
sOp(i) = 2
On Error Resume Next
sCrit2(i) = flt.Criteria2
sOp(i) = flt.Operator
On Error GoTo 0
End If
Next
ActiveSheet.ShowAllData
Set rng1 = rng.Columns(1).Cells
Set rng2 = rng(rng.Count + 1)
Set rng3 = Intersect(rng, ActiveCell.EntireRow)
rng3.Copy rng2
Set rng = rng.Resize(rng.Rows.Count + 1)
ActiveSheet.AutoFilterMode = False
rng.AutoFilter
For i = 1 To cnt
If sCrit1(i) < "" Then
If sCrit2(i) < "" Then
rng.AutoFilter Field:=i, Criteria1:=sCrit1(i), _
Operator:=sOp(i), _
Criteria2:=sCrit2(i)
Else
rng.AutoFilter Field:=i, Criteria1:=sCrit1(i)
End If
End If
Next
End If
End Sub

--
Regards,
Tom Ogilvy


"Sarah" wrote:

That sounds like it'ss work. Do you know the command for finding out what
was selected for a filter?

"Tom Ogilvy" wrote:

After the commandbutton code copies the row, it should remove the autofilter
or showAll and reapply it with the users name as the criteria.

--
Regards,
Tom Ogilvy


"Sarah" wrote:

I have a worksheet that has an autofilter on a column called Assigned DFE.
When a user uses this filter they will just see the rows that pertain to
them. I created a command button that allows them copy one of their existing
rows to the next available row, but when this happens they must re-select
their name in the Assigned DFE column in order to see the new row that was
added. How can I get my command button to automatically show the new row
that was added without them having to do that extra step?

Thank you,

Sarah

Re-set autofilter selection after adding a new row - programma
 

I gave you the information.

It is stored in three methods of the filter object

Criteria1
Criteria2
Operator

If the user will only select an item from the dropdown, then you only need
to look at Criteria1.

--
Regards,
Tom Ogilvy


"Sarah" wrote in message
...
That was a bit more than I was looking for. I'm not that good with
coding.
So there isn't a command that tells me what was selected for an
autofilter?

For example I have a column called DFE. i've applied the autfilter to it
and
now when the user makes a selection, I would like to know where this
selection is stored so that I can access it in the code.


"Tom Ogilvy" wrote:

If the user has selected a cell on a visible row in the autofilter and
presses the command button, it will copy that row to the bottom of the
filter
range and reapply the filter to include that row and the existing
criteria.

Private Sub Commandbutton1_Click()
Dim flt As Filter
Dim sCrit1() As String, sCrit2() As String
Dim sOp() As Long, rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim i As Long, cnt As Long
Dim rng4 As Range
If ActiveSheet.AutoFilterMode Then
Set rng = ActiveSheet.AutoFilter.Range
Set rng4 = Intersect(rng, ActiveCell)
If ActiveCell.Row = rng.Row Or _
rng4 Is Nothing Or _
ActiveCell.EntireRow.Hidden Then
MsgBox "Select a visible row to reproduce" & _
"in the filtered data"
Exit Sub
End If
i = 0
cnt = ActiveSheet.AutoFilter.Filters.Count
ReDim sCrit1(1 To cnt)
ReDim sCrit2(1 To cnt)
ReDim sOp(1 To cnt)
For Each flt In ActiveSheet.AutoFilter.Filters
i = i + 1
If flt.On Then
sCrit1(i) = flt.Criteria1
sCrit2(i) = ""
sOp(i) = 2
On Error Resume Next
sCrit2(i) = flt.Criteria2
sOp(i) = flt.Operator
On Error GoTo 0
End If
Next
ActiveSheet.ShowAllData
Set rng1 = rng.Columns(1).Cells
Set rng2 = rng(rng.Count + 1)
Set rng3 = Intersect(rng, ActiveCell.EntireRow)
rng3.Copy rng2
Set rng = rng.Resize(rng.Rows.Count + 1)
ActiveSheet.AutoFilterMode = False
rng.AutoFilter
For i = 1 To cnt
If sCrit1(i) < "" Then
If sCrit2(i) < "" Then
rng.AutoFilter Field:=i, Criteria1:=sCrit1(i), _
Operator:=sOp(i), _
Criteria2:=sCrit2(i)
Else
rng.AutoFilter Field:=i, Criteria1:=sCrit1(i)
End If
End If
Next
End If
End Sub

--
Regards,
Tom Ogilvy


"Sarah" wrote:

That sounds like it'ss work. Do you know the command for finding out
what
was selected for a filter?

"Tom Ogilvy" wrote:

After the commandbutton code copies the row, it should remove the
autofilter
or showAll and reapply it with the users name as the criteria.

--
Regards,
Tom Ogilvy


"Sarah" wrote:

I have a worksheet that has an autofilter on a column called
Assigned DFE.
When a user uses this filter they will just see the rows that
pertain to
them. I created a command button that allows them copy one of
their existing
rows to the next available row, but when this happens they must
re-select
their name in the Assigned DFE column in order to see the new row
that was
added. How can I get my command button to automatically show the
new row
that was added without them having to do that extra step?

Thank you,

Sarah

Re-set autofilter selection after adding a new row - programma
 

Thanks Tom. I wasn't seeing it before. That worked great!

"Tom Ogilvy" wrote:

I gave you the information.

It is stored in three methods of the filter object

Criteria1
Criteria2
Operator

If the user will only select an item from the dropdown, then you only need
to look at Criteria1.

--
Regards,
Tom Ogilvy


"Sarah" wrote in message
...
That was a bit more than I was looking for. I'm not that good with
coding.
So there isn't a command that tells me what was selected for an
autofilter?

For example I have a column called DFE. i've applied the autfilter to it
and
now when the user makes a selection, I would like to know where this
selection is stored so that I can access it in the code.


"Tom Ogilvy" wrote:

If the user has selected a cell on a visible row in the autofilter and
presses the command button, it will copy that row to the bottom of the
filter
range and reapply the filter to include that row and the existing
criteria.

Private Sub Commandbutton1_Click()
Dim flt As Filter
Dim sCrit1() As String, sCrit2() As String
Dim sOp() As Long, rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim i As Long, cnt As Long
Dim rng4 As Range
If ActiveSheet.AutoFilterMode Then
Set rng = ActiveSheet.AutoFilter.Range
Set rng4 = Intersect(rng, ActiveCell)
If ActiveCell.Row = rng.Row Or _
rng4 Is Nothing Or _
ActiveCell.EntireRow.Hidden Then
MsgBox "Select a visible row to reproduce" & _
"in the filtered data"
Exit Sub
End If
i = 0
cnt = ActiveSheet.AutoFilter.Filters.Count
ReDim sCrit1(1 To cnt)
ReDim sCrit2(1 To cnt)
ReDim sOp(1 To cnt)
For Each flt In ActiveSheet.AutoFilter.Filters
i = i + 1
If flt.On Then
sCrit1(i) = flt.Criteria1
sCrit2(i) = ""
sOp(i) = 2
On Error Resume Next
sCrit2(i) = flt.Criteria2
sOp(i) = flt.Operator
On Error GoTo 0
End If
Next
ActiveSheet.ShowAllData
Set rng1 = rng.Columns(1).Cells
Set rng2 = rng(rng.Count + 1)
Set rng3 = Intersect(rng, ActiveCell.EntireRow)
rng3.Copy rng2
Set rng = rng.Resize(rng.Rows.Count + 1)
ActiveSheet.AutoFilterMode = False
rng.AutoFilter
For i = 1 To cnt
If sCrit1(i) < "" Then
If sCrit2(i) < "" Then
rng.AutoFilter Field:=i, Criteria1:=sCrit1(i), _
Operator:=sOp(i), _
Criteria2:=sCrit2(i)
Else
rng.AutoFilter Field:=i, Criteria1:=sCrit1(i)
End If
End If
Next
End If
End Sub

--
Regards,
Tom Ogilvy


"Sarah" wrote:

That sounds like it'ss work. Do you know the command for finding out
what
was selected for a filter?

"Tom Ogilvy" wrote:

After the commandbutton code copies the row, it should remove the
autofilter
or showAll and reapply it with the users name as the criteria.

--
Regards,
Tom Ogilvy


"Sarah" wrote:

I have a worksheet that has an autofilter on a column called
Assigned DFE.
When a user uses this filter they will just see the rows that
pertain to
them. I created a command button that allows them copy one of
their existing
rows to the next available row, but when this happens they must
re-select
their name in the Assigned DFE column in order to see the new row
that was
added. How can I get my command button to automatically show the
new row
that was added without them having to do that extra step?

Thank you,

Sarah