Hi Guys,

I have designed a userform which writes data to a master excel spreadsheet.
I have tried the autofilter method but am hoplessly lost.

At the beginning of each day I would like each user to get their work for
the day and exit the masterfile. To do this I have to filter the masterfile
by:
agent_name (the agents name in a textbox)
task_due_date (equal to today)
final_status (is "open)
which are all in different columns and then copy the entire row of data to
their personal worksheet. The selected row must then be deleted so that when
the updated(resolved) case is exported at the end of the day, no duplicates
exist in the masterfile. This data then is accessed using a formlistbox and
manipulated using the form controls.
Also I would like to use the count function to count the number of
tasks/records the agent has for the day, but this must decrease every time
they access and close outstanding work.

Can anyone help?

Thanks
Albert

Hi Albert

See if this page will help you
http://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Albert" wrote in message ...
Hi Guys,

I have designed a userform which writes data to a master excel spreadsheet.
I have tried the autofilter method but am hoplessly lost.

At the beginning of each day I would like each user to get their work for
the day and exit the masterfile. To do this I have to filter the masterfile
by:
agent_name (the agents name in a textbox)
task_due_date (equal to today)
final_status (is "open)
which are all in different columns and then copy the entire row of data to
their personal worksheet. The selected row must then be deleted so that when
the updated(resolved) case is exported at the end of the day, no duplicates
exist in the masterfile. This data then is accessed using a formlistbox and
manipulated using the form controls.
Also I would like to use the count function to count the number of
tasks/records the agent has for the day, but this must decrease every time
they access and close outstanding work.

Can anyone help?

Thanks
Albert

Hi Ron,

This helps emmensly. I was using your code that you answered to another
question.

I have two more questions though:

1. The ws object does not seem to work with the excel vb? Obviously I am not
decalring it correctly or is there a reference that I need to select in
tools? I noticed some other code referring to "ws" as "wks". Which one should
I use.
2. I am also accessing a closed excel file (masterfile) do I use the "get"
statement also from your previous code?

Thanks
A

"Ron de Bruin" wrote:

Hi Albert

See if this page will help you
http://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Albert" wrote in message ...
Hi Guys,

I have designed a userform which writes data to a master excel spreadsheet.
I have tried the autofilter method but am hoplessly lost.

At the beginning of each day I would like each user to get their work for
the day and exit the masterfile. To do this I have to filter the masterfile
by:
agent_name (the agents name in a textbox)
task_due_date (equal to today)
final_status (is "open)
which are all in different columns and then copy the entire row of data to
their personal worksheet. The selected row must then be deleted so that when
the updated(resolved) case is exported at the end of the day, no duplicates
exist in the masterfile. This data then is accessed using a formlistbox and
manipulated using the form controls.
Also I would like to use the count function to count the number of
tasks/records the agent has for the day, but this must decrease every time
they access and close outstanding work.

Can anyone help?

Thanks
Albert

The code is working without setting a reference
Do you copy the code in the correct place

Which example do you use and what is the problem you have
Tell us what you have done

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Albert" wrote in message ...
Hi Ron,

This helps emmensly. I was using your code that you answered to another
question.

I have two more questions though:

1. The ws object does not seem to work with the excel vb? Obviously I am not
decalring it correctly or is there a reference that I need to select in
tools? I noticed some other code referring to "ws" as "wks". Which one should
I use.
2. I am also accessing a closed excel file (masterfile) do I use the "get"
statement also from your previous code?

Thanks
A

"Ron de Bruin" wrote:

Hi Albert

See if this page will help you
http://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Albert" wrote in message ...
Hi Guys,

I have designed a userform which writes data to a master excel spreadsheet.
I have tried the autofilter method but am hoplessly lost.

At the beginning of each day I would like each user to get their work for
the day and exit the masterfile. To do this I have to filter the masterfile
by:
agent_name (the agents name in a textbox)
task_due_date (equal to today)
final_status (is "open)
which are all in different columns and then copy the entire row of data to
their personal worksheet. The selected row must then be deleted so that when
the updated(resolved) case is exported at the end of the day, no duplicates
exist in the masterfile. This data then is accessed using a formlistbox and
manipulated using the form controls.
Also I would like to use the count function to count the number of
tasks/records the agent has for the day, but this must decrease every time
they access and close outstanding work.

Can anyone help?

Thanks
Albert

Hi Ron,

Here is my code can you help?

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
Dim sourceWB As Workbook

With Application
.ScreenUpdating = False
.EnableEvents = False
End With


If bIsBookOpen("Test DB.xlsm") Then
Set destWB = Workbooks("Test DB.xlsm")
Else
Set destWB = Workbooks.Open("C:\Documents and Settings\albertr\My
Documents\Test Database\Test DB.xlsm")
End If

Set WS = Sheets("Sheet1")


Set rng = WS.Range("A1:D" & Rows.Count) '<<<<the range stops at column D

WS.AutoFilterMode = False

On Error Resume Next
Application.DisplayAlerts = False
Sheets("MyFilterResult").Delete
Application.DisplayAlerts = True
On Error GoTo 0

rng.AutoFilter Field:=3, Criteria1:="=" & ComboBoxCustomerAgent.Value
rng.AutoFilter Field:=14, Criteria1:="<=" & DTPicker1.Value '<<< I get
an error here
rng.AutoFilter Field:=18, Criteria1:="= Open" '<<<< I get an error here

Set WSNew = Workbooks("Customer services test.xlsm").Worksheets("Sheet2")

WS.AutoFilter.Range.Copy

With WSNew.Range("A2")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
.Select ' <<<<<<I get an error here
Application.CutCopyMode = False

End With

' This does not seem to work
With WS.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then rng2.EntireRow.Delete
End With

WS.AutoFilterMode = False
destWB.Close SaveChanges:=True
With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub


"Ron de Bruin" wrote:

The code is working without setting a reference
Do you copy the code in the correct place

Which example do you use and what is the problem you have
Tell us what you have done

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Albert" wrote in message ...
Hi Ron,

This helps emmensly. I was using your code that you answered to another
question.

I have two more questions though:

1. The ws object does not seem to work with the excel vb? Obviously I am not
decalring it correctly or is there a reference that I need to select in
tools? I noticed some other code referring to "ws" as "wks". Which one should
I use.
2. I am also accessing a closed excel file (masterfile) do I use the "get"
statement also from your previous code?

Thanks
A

"Ron de Bruin" wrote:

Hi Albert

See if this page will help you
http://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Albert" wrote in message ...
Hi Guys,

I have designed a userform which writes data to a master excel spreadsheet.
I have tried the autofilter method but am hoplessly lost.

At the beginning of each day I would like each user to get their work for
the day and exit the masterfile. To do this I have to filter the masterfile
by:
agent_name (the agents name in a textbox)
task_due_date (equal to today)
final_status (is "open)
which are all in different columns and then copy the entire row of data to
their personal worksheet. The selected row must then be deleted so that when
the updated(resolved) case is exported at the end of the day, no duplicates
exist in the masterfile. This data then is accessed using a formlistbox and
manipulated using the form controls.
Also I would like to use the count function to count the number of
tasks/records the agent has for the day, but this must decrease every time
they access and close outstanding work.

Can anyone help?

Thanks
Albert

Hi Albert

Your range have only four columns
So Filter fiels 14 and 18 is not possible

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Albert" wrote in message ...
Hi Ron,

Here is my code can you help?

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
Dim sourceWB As Workbook

With Application
.ScreenUpdating = False
.EnableEvents = False
End With


If bIsBookOpen("Test DB.xlsm") Then
Set destWB = Workbooks("Test DB.xlsm")
Else
Set destWB = Workbooks.Open("C:\Documents and Settings\albertr\My
Documents\Test Database\Test DB.xlsm")
End If

Set WS = Sheets("Sheet1")


Set rng = WS.Range("A1:D" & Rows.Count) '<<<<the range stops at column D

WS.AutoFilterMode = False

On Error Resume Next
Application.DisplayAlerts = False
Sheets("MyFilterResult").Delete
Application.DisplayAlerts = True
On Error GoTo 0

rng.AutoFilter Field:=3, Criteria1:="=" & ComboBoxCustomerAgent.Value
rng.AutoFilter Field:=14, Criteria1:="<=" & DTPicker1.Value '<<< I get
an error here
rng.AutoFilter Field:=18, Criteria1:="= Open" '<<<< I get an error here

Set WSNew = Workbooks("Customer services test.xlsm").Worksheets("Sheet2")

WS.AutoFilter.Range.Copy

With WSNew.Range("A2")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
.Select ' <<<<<<I get an error here
Application.CutCopyMode = False

End With

' This does not seem to work
With WS.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng2 Is Nothing Then rng2.EntireRow.Delete
End With

WS.AutoFilterMode = False
destWB.Close SaveChanges:=True
With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub


"Ron de Bruin" wrote:

The code is working without setting a reference
Do you copy the code in the correct place

Which example do you use and what is the problem you have
Tell us what you have done

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Albert" wrote in message ...
Hi Ron,

This helps emmensly. I was using your code that you answered to another
question.

I have two more questions though:

1. The ws object does not seem to work with the excel vb? Obviously I am not
decalring it correctly or is there a reference that I need to select in
tools? I noticed some other code referring to "ws" as "wks". Which one should
I use.
2. I am also accessing a closed excel file (masterfile) do I use the "get"
statement also from your previous code?

Thanks
A

"Ron de Bruin" wrote:

Hi Albert

See if this page will help you
http://www.rondebruin.nl/copy5.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Albert" wrote in message ...
Hi Guys,

I have designed a userform which writes data to a master excel spreadsheet.
I have tried the autofilter method but am hoplessly lost.

At the beginning of each day I would like each user to get their work for
the day and exit the masterfile. To do this I have to filter the masterfile
by:
agent_name (the agents name in a textbox)
task_due_date (equal to today)
final_status (is "open)
which are all in different columns and then copy the entire row of data to
their personal worksheet. The selected row must then be deleted so that when
the updated(resolved) case is exported at the end of the day, no duplicates
exist in the masterfile. This data then is accessed using a formlistbox and
manipulated using the form controls.
Also I would like to use the count function to count the number of
tasks/records the agent has for the day, but this must decrease every time
they access and close outstanding work.

Can anyone help?

Thanks
Albert