User Form writing slow to worksheet

[rclark]

I have reviewed the similar posts and none of the recommendations fix my issue. I have a form with several text boxes that I'm trying to write to a sheet (20 lines, 6 cells per line).
It takes about 58 seconds to write this to the sheet but takes a fraction of a second to load the form from the same table. I feel like I've done this before with no issue but this is clunky. Can some one set me on the right path to speed this thing up? Below code is in the form.

Private Sub cmd_Done_Click()
    ' Turn stuff off
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    ' Dims
    Dim i As Integer
    Dim j As Integer
    Dim LastRow As Long
    Dim myRow As Long
    Dim myPillar(1 To 4) As String
        myPillar(1) = "Q"
        myPillar(2) = "S"
        myPillar(3) = "D"
        myPillar(4) = "C"
    ' Load data from form into sheet
    With Sheets("KPI Table")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For j = 1 To 4
            For i = 1 To 5
            myRow = Me.Controls("lbl_Row_KPI_" & myPillar(j) & "_" & Format(i, "00"))
                    .Cells(myRow, 1).Value = "KPI_" & myPillar(j) & "_" & Format(i, "00")
                    .Cells(myRow, 2).Value = Me.lbl_Date
                    .Cells(myRow, 3).Value = Mid(Me.lbl_Date, Len(Me.lbl_Date) - 3)
                    .Cells(myRow, 4).Value = "YrWk"
                    .Cells(myRow, 5).Value = Me.Controls("tbo_G_KPI_" & myPillar(j) & "_" & Format(i, "00")).Value
                    .Cells(myRow, 6).Value = Me.Controls("tbo_A_KPI_" & myPillar(j) & "_" & Format(i, "00")).Value
            Next i
        Next j
    End With
    ' Turn stuff back on
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Unload Me
End Sub

 

[James006]

Hi,

Your macro looks fine ...

Probably your sheet is storing a huge amount of blank cells ...

A quick fix with End Home :

https://support.office.com/en-us/ar...orksheet-C9E468A8-0FC3-4F69-8038-B3C1D86E99E9

Hope this will help

 

[rclark]

I went through every sheet and cleared all formatting but the code still runs the same speed. There are several charts in the workbook as well as pictures referencing ranges but I thought that turning off EnableEvents would keep all this from trying to update. I even recoded to input the data as a string and then split it out when initialized. This reduced the speed to about 1/6 (as expected going from 6 cells to 1 per line) but that makes the data useless for the charts and formulas. I deleted all of the other sheets for a test and the code ran fine, so it is something in the sheet but I can't figure out what it is.

 

[rclark]

I've isolated the issue to images that are referencing ranges. This is where you take a range, "Copy as Picture", paste on another sheet and in the formula bar, put "=(range of pic) so that it auto updates. When I remove these the code works well. Is there anything that can speed this up WITH the referenced pics in place?

 

[James006]

Hi,

When it comes to pictures, you can do a couple of things:

1. Reduce their sizes to fit your display requirements

2. Store them in a folder outside of your workbook

Hope this will help