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" <(E-Mail Removed)> wrote in message news:85C16A06-EAD1-462C-8E18-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:85C16A06-EAD1-462C-8E18-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news:FB6DE3EA-3A94-4DA0-B0FC-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:85C16A06-EAD1-462C-8E18-(E-Mail Removed)...
>> > 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" & 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" <(E-Mail Removed)> wrote in message news:FB6DE3EA-3A94-4DA0-B0FC-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message news:85C16A06-EAD1-462C-8E18-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message news:67A9B31F-FD19-49CA-A43C-(E-Mail Removed)...
> 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" & 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" <(E-Mail Removed)> wrote in message news:FB6DE3EA-3A94-4DA0-B0FC-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message news:85C16A06-EAD1-462C-8E18-(E-Mail Removed)...
>> >> > 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,

What code do I use to increase the range?
Also what about the paste command?

"Ron de Bruin" wrote:

> 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" <(E-Mail Removed)> wrote in message news:67A9B31F-FD19-49CA-A43C-(E-Mail Removed)...
> > 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" & 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" <(E-Mail Removed)> wrote in message news:FB6DE3EA-3A94-4DA0-B0FC-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message news:85C16A06-EAD1-462C-8E18-(E-Mail Removed)...
> >> >> > 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,

What must I change on this line to increase the range?
Set rng = WS.Range("A1" & Rows.Count)

"Ron de Bruin" wrote:

> 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" <(E-Mail Removed)> wrote in message news:67A9B31F-FD19-49CA-A43C-(E-Mail Removed)...
> > 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" & 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" <(E-Mail Removed)> wrote in message news:FB6DE3EA-3A94-4DA0-B0FC-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message news:85C16A06-EAD1-462C-8E18-(E-Mail Removed)...
> >> >> > 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
> >> >> >
> >> >>
> >>
>

D is the last column in the Filter range

So change it to column 18 = R

Set rng = WS.Range("A1:R" & Rows.Count)


--

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


"Albert" <(E-Mail Removed)> wrote in message news:BE5841AA-B1D8-48AC-BEE5-(E-Mail Removed)...
> Hi Ron,
>
> What must I change on this line to increase the range?
> Set rng = WS.Range("A1" & Rows.Count)
>
> "Ron de Bruin" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message news:67A9B31F-FD19-49CA-A43C-(E-Mail Removed)...
>> > 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" & 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" <(E-Mail Removed)> wrote in message news:FB6DE3EA-3A94-4DA0-B0FC-(E-Mail Removed)...
>> >> > 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" <(E-Mail Removed)> wrote in message news:85C16A06-EAD1-462C-8E18-(E-Mail Removed)...
>> >> >> > 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,

Thanks it works like a charm.

How would i count the rows copied?

Albert

"Ron de Bruin" wrote:

> D is the last column in the Filter range
>
> So change it to column 18 = R
>
> Set rng = WS.Range("A1:R" & Rows.Count)
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Albert" <(E-Mail Removed)> wrote in message news:BE5841AA-B1D8-48AC-BEE5-(E-Mail Removed)...
> > Hi Ron,
> >
> > What must I change on this line to increase the range?
> > Set rng = WS.Range("A1" & Rows.Count)
> >
> > "Ron de Bruin" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message news:67A9B31F-FD19-49CA-A43C-(E-Mail Removed)...
> >> > 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" & 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" <(E-Mail Removed)> wrote in message news:FB6DE3EA-3A94-4DA0-B0FC-(E-Mail Removed)...
> >> >> > 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" <(E-Mail Removed)> wrote in message news:85C16A06-EAD1-462C-8E18-(E-Mail Removed)...
> >> >> >> > 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
> >> >> >> >
> >> >> >>
> >> >>
> >>
>