Long Time Delay To Paste From UserForm To Sheet

[Minitman]

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]

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]

Hey Dave,

Thanks for replying.

How do I turn off events?

-Minitman

 

[Dave Peterson]

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

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

 

[Minitman]

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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

I don't have any other guesses.

Maybe someone else will jump in with a suggestion.