Long Time Delay To Paste From UserForm To Sheet

Greetings,

I have a UserForm with 41TextBoxes on it. I am using the Offset
method to transfer the value of each box to the last row of the column
that the data belongs in. I then clear cells with only 0's in them.
And last I sort the sheet. This is taking about a minute each cycle
and getting longer.

Here is the code that I use:

Set ExtWB2 = Workbooks("1993-12.xls")
Set ExtWS2 = ExtWB2.Worksheets("Customers")

ExtWS2.Activate

With Range("A65536").End(xlUp).Offset(1, 0)
.Value = CB1.Text
.Offset(0, 2).Value = TB2.Value
.Offset(0, 3).Value = TB3.Value
<snip 4 - 40>
.Offset(0, 41).Value = TB41.Value
End With

Cells.Select
'This looks for cells with only one 0 in them and clears them
Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
:=False, ReplaceFormat:=False
'This sorts the sheet by the A column
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header _
:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation _
:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal, DataOption3:=xlSortNormal
Range("A1").Select

It is taking a minute to cycle one name. I have about 600 to do and
that is going to take a LONG time to finish.

Anyone have any ideas as to how to increase the speed of this sub?

TIA

-Minitman

Drop the cleaning up/sorting the cells until you're done with all the data
entry.

Make sure that events are disabled before you write to the worksheet.

Turn calculation off until you're almost done.

Minitman wrote:
>
> Greetings,
>
> I have a UserForm with 41TextBoxes on it. I am using the Offset
> method to transfer the value of each box to the last row of the column
> that the data belongs in. I then clear cells with only 0's in them.
> And last I sort the sheet. This is taking about a minute each cycle
> and getting longer.
>
> Here is the code that I use:
>
> Set ExtWB2 = Workbooks("1993-12.xls")
> Set ExtWS2 = ExtWB2.Worksheets("Customers")
>
> ExtWS2.Activate
>
> With Range("A65536").End(xlUp).Offset(1, 0)
> .Value = CB1.Text
> .Offset(0, 2).Value = TB2.Value
> .Offset(0, 3).Value = TB3.Value
> <snip 4 - 40>
> .Offset(0, 41).Value = TB41.Value
> End With
>
> Cells.Select
> 'This looks for cells with only one 0 in them and clears them
> Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
> :=False, ReplaceFormat:=False
> 'This sorts the sheet by the A column
> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header _
> :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation _
> :=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
> :=xlSortNormal, DataOption3:=xlSortNormal
> Range("A1").Select
>
> It is taking a minute to cycle one name. I have about 600 to do and
> that is going to take a LONG time to finish.
>
> Anyone have any ideas as to how to increase the speed of this sub?
>
> TIA
>
> -Minitman

--

Dave Peterson

Hey Dave,

Thanks for replying.

How do I turn off events?

-Minitman



On Mon, 05 Dec 2005 12:44:46 -0600, Dave Peterson
<[email protected]> wrote:

>Drop the cleaning up/sorting the cells until you're done with all the data
>entry.
>
>Make sure that events are disabled before you write to the worksheet.
>
>Turn calculation off until you're almost done.
>
>Minitman wrote:
>>
>> Greetings,
>>
>> I have a UserForm with 41TextBoxes on it. I am using the Offset
>> method to transfer the value of each box to the last row of the column
>> that the data belongs in. I then clear cells with only 0's in them.
>> And last I sort the sheet. This is taking about a minute each cycle
>> and getting longer.
>>
>> Here is the code that I use:
>>
>> Set ExtWB2 = Workbooks("1993-12.xls")
>> Set ExtWS2 = ExtWB2.Worksheets("Customers")
>>
>> ExtWS2.Activate
>>
>> With Range("A65536").End(xlUp).Offset(1, 0)
>> .Value = CB1.Text
>> .Offset(0, 2).Value = TB2.Value
>> .Offset(0, 3).Value = TB3.Value
>> <snip 4 - 40>
>> .Offset(0, 41).Value = TB41.Value
>> End With
>>
>> Cells.Select
>> 'This looks for cells with only one 0 in them and clears them
>> Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
>> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
>> :=False, ReplaceFormat:=False
>> 'This sorts the sheet by the A column
>> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header _
>> :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation _
>> :=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
>> :=xlSortNormal, DataOption3:=xlSortNormal
>> Range("A1").Select
>>
>> It is taking a minute to cycle one name. I have about 600 to do and
>> that is going to take a LONG time to finish.
>>
>> Anyone have any ideas as to how to increase the speed of this sub?
>>
>> TIA
>>
>> -Minitman

Application.enableevents = false
'do a bunch of stuff
application.enableevents = true

This stops those worksheet_change events (among others) from firing.

Minitman wrote:
>
> Hey Dave,
>
> Thanks for replying.
>
> How do I turn off events?
>
> -Minitman
>
> On Mon, 05 Dec 2005 12:44:46 -0600, Dave Peterson
> <[email protected]> wrote:
>
> >Drop the cleaning up/sorting the cells until you're done with all the data
> >entry.
> >
> >Make sure that events are disabled before you write to the worksheet.
> >
> >Turn calculation off until you're almost done.
> >
> >Minitman wrote:
> >>
> >> Greetings,
> >>
> >> I have a UserForm with 41TextBoxes on it. I am using the Offset
> >> method to transfer the value of each box to the last row of the column
> >> that the data belongs in. I then clear cells with only 0's in them.
> >> And last I sort the sheet. This is taking about a minute each cycle
> >> and getting longer.
> >>
> >> Here is the code that I use:
> >>
> >> Set ExtWB2 = Workbooks("1993-12.xls")
> >> Set ExtWS2 = ExtWB2.Worksheets("Customers")
> >>
> >> ExtWS2.Activate
> >>
> >> With Range("A65536").End(xlUp).Offset(1, 0)
> >> .Value = CB1.Text
> >> .Offset(0, 2).Value = TB2.Value
> >> .Offset(0, 3).Value = TB3.Value
> >> <snip 4 - 40>
> >> .Offset(0, 41).Value = TB41.Value
> >> End With
> >>
> >> Cells.Select
> >> 'This looks for cells with only one 0 in them and clears them
> >> Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
> >> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
> >> :=False, ReplaceFormat:=False
> >> 'This sorts the sheet by the A column
> >> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header _
> >> :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation _
> >> :=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
> >> :=xlSortNormal, DataOption3:=xlSortNormal
> >> Range("A1").Select
> >>
> >> It is taking a minute to cycle one name. I have about 600 to do and
> >> that is going to take a LONG time to finish.
> >>
> >> Anyone have any ideas as to how to increase the speed of this sub?
> >>
> >> TIA
> >>
> >> -Minitman

--

Dave Peterson

Hey Dave,

I just tried it. It took 1:09 without the events disabled and 1:11
with events disabled(?).

Is there a more efficient way to get the data from the UserForm to the
worksheet?

This is getting very frustrating!

Any ideas on alternatives are most appreciated.

TIA

-Minitman



On Mon, 05 Dec 2005 16:19:21 -0600, Dave Peterson
<[email protected]> wrote:

>Application.enableevents = false
>'do a bunch of stuff
>application.enableevents = true
>
>This stops those worksheet_change events (among others) from firing.
>
>Minitman wrote:
>>
>> Hey Dave,
>>
>> Thanks for replying.
>>
>> How do I turn off events?
>>
>> -Minitman
>>
>> On Mon, 05 Dec 2005 12:44:46 -0600, Dave Peterson
>> <[email protected]> wrote:
>>
>> >Drop the cleaning up/sorting the cells until you're done with all the data
>> >entry.
>> >
>> >Make sure that events are disabled before you write to the worksheet.
>> >
>> >Turn calculation off until you're almost done.
>> >
>> >Minitman wrote:
>> >>
>> >> Greetings,
>> >>
>> >> I have a UserForm with 41TextBoxes on it. I am using the Offset
>> >> method to transfer the value of each box to the last row of the column
>> >> that the data belongs in. I then clear cells with only 0's in them.
>> >> And last I sort the sheet. This is taking about a minute each cycle
>> >> and getting longer.
>> >>
>> >> Here is the code that I use:
>> >>
>> >> Set ExtWB2 = Workbooks("1993-12.xls")
>> >> Set ExtWS2 = ExtWB2.Worksheets("Customers")
>> >>
>> >> ExtWS2.Activate
>> >>
>> >> With Range("A65536").End(xlUp).Offset(1, 0)
>> >> .Value = CB1.Text
>> >> .Offset(0, 2).Value = TB2.Value
>> >> .Offset(0, 3).Value = TB3.Value
>> >> <snip 4 - 40>
>> >> .Offset(0, 41).Value = TB41.Value
>> >> End With
>> >>
>> >> Cells.Select
>> >> 'This looks for cells with only one 0 in them and clears them
>> >> Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
>> >> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
>> >> :=False, ReplaceFormat:=False
>> >> 'This sorts the sheet by the A column
>> >> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header _
>> >> :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation _
>> >> :=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
>> >> :=xlSortNormal, DataOption3:=xlSortNormal
>> >> Range("A1").Select
>> >>
>> >> It is taking a minute to cycle one name. I have about 600 to do and
>> >> that is going to take a LONG time to finish.
>> >>
>> >> Anyone have any ideas as to how to increase the speed of this sub?
>> >>
>> >> TIA
>> >>
>> >> -Minitman

This code shouldn't take a minute to execute:

With Range("A65536").End(xlUp).Offset(1, 0)
.Value = CB1.Text
.Offset(0, 2).Value = TB2.Value
.Offset(0, 3).Value = TB3.Value
<snip 4 - 40>
.Offset(0, 41).Value = TB41.Value
End With

Well, maybe the snipped portion does more than you want.

Minitman wrote:
>
> Hey Dave,
>
> I just tried it. It took 1:09 without the events disabled and 1:11
> with events disabled(?).
>
> Is there a more efficient way to get the data from the UserForm to the
> worksheet?
>
> This is getting very frustrating!
>
> Any ideas on alternatives are most appreciated.
>
> TIA
>
> -Minitman
>
>
>
> On Mon, 05 Dec 2005 16:19:21 -0600, Dave Peterson
> <[email protected]> wrote:
>
> >Application.enableevents = false
> >'do a bunch of stuff
> >application.enableevents = true
> >
> >This stops those worksheet_change events (among others) from firing.
> >
> >Minitman wrote:
> >>
> >> Hey Dave,
> >>
> >> Thanks for replying.
> >>
> >> How do I turn off events?
> >>
> >> -Minitman
> >>
> >> On Mon, 05 Dec 2005 12:44:46 -0600, Dave Peterson
> >> <[email protected]> wrote:
> >>
> >> >Drop the cleaning up/sorting the cells until you're done with all the data
> >> >entry.
> >> >
> >> >Make sure that events are disabled before you write to the worksheet.
> >> >
> >> >Turn calculation off until you're almost done.
> >> >
> >> >Minitman wrote:
> >> >>
> >> >> Greetings,
> >> >>
> >> >> I have a UserForm with 41TextBoxes on it. I am using the Offset
> >> >> method to transfer the value of each box to the last row of the column
> >> >> that the data belongs in. I then clear cells with only 0's in them.
> >> >> And last I sort the sheet. This is taking about a minute each cycle
> >> >> and getting longer.
> >> >>
> >> >> Here is the code that I use:
> >> >>
> >> >> Set ExtWB2 = Workbooks("1993-12.xls")
> >> >> Set ExtWS2 = ExtWB2.Worksheets("Customers")
> >> >>
> >> >> ExtWS2.Activate
> >> >>
> >> >> With Range("A65536").End(xlUp).Offset(1, 0)
> >> >> .Value = CB1.Text
> >> >> .Offset(0, 2).Value = TB2.Value
> >> >> .Offset(0, 3).Value = TB3.Value
> >> >> <snip 4 - 40>
> >> >> .Offset(0, 41).Value = TB41.Value
> >> >> End With
> >> >>
> >> >> Cells.Select
> >> >> 'This looks for cells with only one 0 in them and clears them
> >> >> Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
> >> >> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
> >> >> :=False, ReplaceFormat:=False
> >> >> 'This sorts the sheet by the A column
> >> >> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header _
> >> >> :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation _
> >> >> :=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
> >> >> :=xlSortNormal, DataOption3:=xlSortNormal
> >> >> Range("A1").Select
> >> >>
> >> >> It is taking a minute to cycle one name. I have about 600 to do and
> >> >> that is going to take a LONG time to finish.
> >> >>
> >> >> Anyone have any ideas as to how to increase the speed of this sub?
> >> >>
> >> >> TIA
> >> >>
> >> >> -Minitman

--

Dave Peterson

Hey Dave,

I have run into this before. That time I could eliminate the UserForm
altogether. This time, that is not an option. The list is only 60
names per workbook, but I have about 160 workbooks to do.
Is there no other way to get the data from these TextBoxes to the last
row of the sheet? I don't know where else to turn to get an answer.

If you cannot take this any further, I understand.

Thanks for sharing what you could, it is appreciated.

-Minitman


On Mon, 05 Dec 2005 17:55:25 -0600, Dave Peterson
<[email protected]> wrote:

>This code shouldn't take a minute to execute:
>
> With Range("A65536").End(xlUp).Offset(1, 0)
> .Value = CB1.Text
> .Offset(0, 2).Value = TB2.Value
> .Offset(0, 3).Value = TB3.Value
><snip 4 - 40>
> .Offset(0, 41).Value = TB41.Value
> End With
>
>Well, maybe the snipped portion does more than you want.
>
>Minitman wrote:
>>
>> Hey Dave,
>>
>> I just tried it. It took 1:09 without the events disabled and 1:11
>> with events disabled(?).
>>
>> Is there a more efficient way to get the data from the UserForm to the
>> worksheet?
>>
>> This is getting very frustrating!
>>
>> Any ideas on alternatives are most appreciated.
>>
>> TIA
>>
>> -Minitman
>>
>>
>>
>> On Mon, 05 Dec 2005 16:19:21 -0600, Dave Peterson
>> <[email protected]> wrote:
>>
>> >Application.enableevents = false
>> >'do a bunch of stuff
>> >application.enableevents = true
>> >
>> >This stops those worksheet_change events (among others) from firing.
>> >
>> >Minitman wrote:
>> >>
>> >> Hey Dave,
>> >>
>> >> Thanks for replying.
>> >>
>> >> How do I turn off events?
>> >>
>> >> -Minitman
>> >>
>> >> On Mon, 05 Dec 2005 12:44:46 -0600, Dave Peterson
>> >> <[email protected]> wrote:
>> >>
>> >> >Drop the cleaning up/sorting the cells until you're done with all the data
>> >> >entry.
>> >> >
>> >> >Make sure that events are disabled before you write to the worksheet.
>> >> >
>> >> >Turn calculation off until you're almost done.
>> >> >
>> >> >Minitman wrote:
>> >> >>
>> >> >> Greetings,
>> >> >>
>> >> >> I have a UserForm with 41TextBoxes on it. I am using the Offset
>> >> >> method to transfer the value of each box to the last row of the column
>> >> >> that the data belongs in. I then clear cells with only 0's in them.
>> >> >> And last I sort the sheet. This is taking about a minute each cycle
>> >> >> and getting longer.
>> >> >>
>> >> >> Here is the code that I use:
>> >> >>
>> >> >> Set ExtWB2 = Workbooks("1993-12.xls")
>> >> >> Set ExtWS2 = ExtWB2.Worksheets("Customers")
>> >> >>
>> >> >> ExtWS2.Activate
>> >> >>
>> >> >> With Range("A65536").End(xlUp).Offset(1, 0)
>> >> >> .Value = CB1.Text
>> >> >> .Offset(0, 2).Value = TB2.Value
>> >> >> .Offset(0, 3).Value = TB3.Value
>> >> >> <snip 4 - 40>
>> >> >> .Offset(0, 41).Value = TB41.Value
>> >> >> End With
>> >> >>
>> >> >> Cells.Select
>> >> >> 'This looks for cells with only one 0 in them and clears them
>> >> >> Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
>> >> >> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
>> >> >> :=False, ReplaceFormat:=False
>> >> >> 'This sorts the sheet by the A column
>> >> >> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header _
>> >> >> :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation _
>> >> >> :=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
>> >> >> :=xlSortNormal, DataOption3:=xlSortNormal
>> >> >> Range("A1").Select
>> >> >>
>> >> >> It is taking a minute to cycle one name. I have about 600 to do and
>> >> >> that is going to take a LONG time to finish.
>> >> >>
>> >> >> Anyone have any ideas as to how to increase the speed of this sub?
>> >> >>
>> >> >> TIA
>> >> >>
>> >> >> -Minitman

I don't have any other guesses.

Maybe someone else will jump in with a suggestion.

Minitman wrote:
>
> Hey Dave,
>
> I have run into this before. That time I could eliminate the UserForm
> altogether. This time, that is not an option. The list is only 60
> names per workbook, but I have about 160 workbooks to do.
> Is there no other way to get the data from these TextBoxes to the last
> row of the sheet? I don't know where else to turn to get an answer.
>
> If you cannot take this any further, I understand.
>
> Thanks for sharing what you could, it is appreciated.
>
> -Minitman
>
> On Mon, 05 Dec 2005 17:55:25 -0600, Dave Peterson
> <[email protected]> wrote:
>
> >This code shouldn't take a minute to execute:
> >
> > With Range("A65536").End(xlUp).Offset(1, 0)
> > .Value = CB1.Text
> > .Offset(0, 2).Value = TB2.Value
> > .Offset(0, 3).Value = TB3.Value
> ><snip 4 - 40>
> > .Offset(0, 41).Value = TB41.Value
> > End With
> >
> >Well, maybe the snipped portion does more than you want.
> >
> >Minitman wrote:
> >>
> >> Hey Dave,
> >>
> >> I just tried it. It took 1:09 without the events disabled and 1:11
> >> with events disabled(?).
> >>
> >> Is there a more efficient way to get the data from the UserForm to the
> >> worksheet?
> >>
> >> This is getting very frustrating!
> >>
> >> Any ideas on alternatives are most appreciated.
> >>
> >> TIA
> >>
> >> -Minitman
> >>
> >>
> >>
> >> On Mon, 05 Dec 2005 16:19:21 -0600, Dave Peterson
> >> <[email protected]> wrote:
> >>
> >> >Application.enableevents = false
> >> >'do a bunch of stuff
> >> >application.enableevents = true
> >> >
> >> >This stops those worksheet_change events (among others) from firing.
> >> >
> >> >Minitman wrote:
> >> >>
> >> >> Hey Dave,
> >> >>
> >> >> Thanks for replying.
> >> >>
> >> >> How do I turn off events?
> >> >>
> >> >> -Minitman
> >> >>
> >> >> On Mon, 05 Dec 2005 12:44:46 -0600, Dave Peterson
> >> >> <[email protected]> wrote:
> >> >>
> >> >> >Drop the cleaning up/sorting the cells until you're done with all the data
> >> >> >entry.
> >> >> >
> >> >> >Make sure that events are disabled before you write to the worksheet.
> >> >> >
> >> >> >Turn calculation off until you're almost done.
> >> >> >
> >> >> >Minitman wrote:
> >> >> >>
> >> >> >> Greetings,
> >> >> >>
> >> >> >> I have a UserForm with 41TextBoxes on it. I am using the Offset
> >> >> >> method to transfer the value of each box to the last row of the column
> >> >> >> that the data belongs in. I then clear cells with only 0's in them.
> >> >> >> And last I sort the sheet. This is taking about a minute each cycle
> >> >> >> and getting longer.
> >> >> >>
> >> >> >> Here is the code that I use:
> >> >> >>
> >> >> >> Set ExtWB2 = Workbooks("1993-12.xls")
> >> >> >> Set ExtWS2 = ExtWB2.Worksheets("Customers")
> >> >> >>
> >> >> >> ExtWS2.Activate
> >> >> >>
> >> >> >> With Range("A65536").End(xlUp).Offset(1, 0)
> >> >> >> .Value = CB1.Text
> >> >> >> .Offset(0, 2).Value = TB2.Value
> >> >> >> .Offset(0, 3).Value = TB3.Value
> >> >> >> <snip 4 - 40>
> >> >> >> .Offset(0, 41).Value = TB41.Value
> >> >> >> End With
> >> >> >>
> >> >> >> Cells.Select
> >> >> >> 'This looks for cells with only one 0 in them and clears them
> >> >> >> Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
> >> >> >> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat _
> >> >> >> :=False, ReplaceFormat:=False
> >> >> >> 'This sorts the sheet by the A column
> >> >> >> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header _
> >> >> >> :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation _
> >> >> >> :=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
> >> >> >> :=xlSortNormal, DataOption3:=xlSortNormal
> >> >> >> Range("A1").Select
> >> >> >>
> >> >> >> It is taking a minute to cycle one name. I have about 600 to do and
> >> >> >> that is going to take a LONG time to finish.
> >> >> >>
> >> >> >> Anyone have any ideas as to how to increase the speed of this sub?
> >> >> >>
> >> >> >> TIA
> >> >> >>
> >> >> >> -Minitman

--

Dave Peterson