Milis Belajar Excel

[milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Classic

List

Threaded

8 messages Options

Hilman

[milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Selamat malam Om

Setelah hampir 5 tahun gak main-main ama makro, dan saat ini coba pakai makro lagi,

kaget banget saya dengan speed of excecution Excel (office365) yang sangat lambat dibandingkan dengan Excel 2013.

Padahal udah :

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.Calculation = xlCalculationManual

Tapi tetap saja masih sangat lambat.

Di excel sebelumnya yang harusnya cuman 2 sd 3 detik, bisa dieksekusi hingga 90 detik

Mohon pencerahannya om-om semuanya

Thanks in advance atas sharing nya

Regards

Hilman

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

Kid

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Administrator

Hai Hilman,

Coba hindari penggunaan Select, Activate, Selection, ActiveCells, ActiveWorkbook

hindari buka tutup proteksi sheet maupun workbook dengan memanfaatkan userinterfaceonly saat akan mem-protect, supaya VBA bisa tetap bekerja seperti biasa tanpa perlu membuka proteksi.

hindari conditional formatting pada cells dalam jumlah banyak (apalagi di cells yang masih kosong [belum ada datanya])

Coba ditelaah ulang setiap prosesnya, mungkin bisa disusun alur proses yang lebih efisien.

Coba ditelaah ulang setiap baris program di setiap prosesnya, mungkin bisa disusun ulang agar lebih sederhana.

Excel 2016 ke atas adalah era baru Excel setelah era Excel 2003. Jadi Excel 2007 s.d. Excel 2013 bisa disebut versi transisi.

Jadi, perbaikan (tuntutan untuk lebih efisien dalam proses otomasi) VBA juga diperlukan.

Regards,

Kid

On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:

Selamat malam Om
Setelah hampir 5 tahun gak main-main ama makro, dan saat ini coba pakai makro lagi,
kaget banget saya dengan speed of excecution Excel (office365) yang sangat lambat dibandingkan dengan Excel 2013.

Padahal udah :

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Tapi tetap saja masih sangat lambat.

Di excel sebelumnya yang harusnya cuman 2 sd 3 detik, bisa dieksekusi hingga 90 detik

Mohon pencerahannya om-om semuanya

Thanks in advance atas sharing nya

Regards
Hilman

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

Hilman

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

berikut coding nya...

Sub SalinDataPoultry()

Dim RgSumber As Range

Dim wsSumber As Worksheet

Dim wbSumber As Workbook

Dim rgData As Range

Dim wsData As Worksheet

Dim wbData As Workbook

Dim rwData As Long

Dim rg As Range

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.Calculation = xlCalculationManual

Application.PrintCommunication = False

Set wbData = ThisWorkbook

Set wsData = wbData.Worksheets("Daily Production")

rwData = wsData.Range("A1").End(xlDown).Row

Set wbSumber = Workbooks("STOCK 1220-PA (003).xlsx") 'Ganti

Set wsSumber = wbSumber.Worksheets("MASTER")

Set RgSumber = wsSumber.Range("I75:GL75") 'Ganti

For Each rg In RgSumber

If rg.Value > 0 Then

rwData = rwData + 1

wsData.Cells(rwData, 4).Value = "Poultry Old Tower"

wsData.Cells(rwData, 6).Value = "Crumbler"

wsData.Cells(rwData, 7).Value = rg.Value

Select Case wsSumber.Cells(4, rg.Column).Value

Case "Prod."

wsData.Cells(rwData, 5).Value = "Production"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case "Retur"

wsData.Cells(rwData, 5).Value = "Retur"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case "Sales"

wsData.Cells(rwData, 5).Value = "Sales"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case "Depo"

wsData.Cells(rwData, 5).Value = "Depo"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case "Remix"

wsData.Cells(rwData, 5).Value = "Remix"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case vbNullString

wsData.Cells(rwData, 5).Value = "Stock"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case Else

MsgBox "Header tidak ketemu"

Application.ScreenUpdating = True

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

Exit Sub

End Select

End If

If rg.Offset(1, 0).Value > 0 Then

rwData = rwData + 1

wsData.Cells(rwData, 4).Value = "Poultry Old Tower"

wsData.Cells(rwData, 6).Value = "Mash"

wsData.Cells(rwData, 7).Value = rg.Offset(1, 0).Value

Select Case wsSumber.Cells(4, rg.Column).Value

Case "Prod."

wsData.Cells(rwData, 5).Value = "Production"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case "Retur"

wsData.Cells(rwData, 5).Value = "Retur"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case "Sales"

wsData.Cells(rwData, 5).Value = "Sales"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case "Depo"

wsData.Cells(rwData, 5).Value = "Depo"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case "Remix"

wsData.Cells(rwData, 5).Value = "Remix"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case vbNullString

wsData.Cells(rwData, 5).Value = "Stock"

wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value

Case Else

MsgBox "Header tidak ketemu"

Application.ScreenUpdating = True

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

Exit Sub

End Select

End If

Next rg

Application.ScreenUpdating = True

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

Application.PrintCommunication = True

End Sub

select, activate, selection dll, sudah tidak pernah saya gunakan lagi..

Regards

Hilman

On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:

Hai Hilman,

Coba hindari penggunaan Select, Activate, Selection, ActiveCells, ActiveWorkbook

hindari buka tutup proteksi sheet maupun workbook dengan memanfaatkan userinterfaceonly saat akan mem-protect, supaya VBA bisa tetap bekerja seperti biasa tanpa perlu membuka proteksi.

hindari conditional formatting pada cells dalam jumlah banyak (apalagi di cells yang masih kosong [belum ada datanya])

Coba ditelaah ulang setiap prosesnya, mungkin bisa disusun alur proses yang lebih efisien.

Coba ditelaah ulang setiap baris program di setiap prosesnya, mungkin bisa disusun ulang agar lebih sederhana.

Excel 2016 ke atas adalah era baru Excel setelah era Excel 2003. Jadi Excel 2007 s.d. Excel 2013 bisa disebut versi transisi.

Jadi, perbaikan (tuntutan untuk lebih efisien dalam proses otomasi) VBA juga diperlukan.

Regards,

Kid

On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:

Selamat malam Om
Setelah hampir 5 tahun gak main-main ama makro, dan saat ini coba pakai makro lagi,
kaget banget saya dengan speed of excecution Excel (office365) yang sangat lambat dibandingkan dengan Excel 2013.

Padahal udah :

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Tapi tetap saja masih sangat lambat.

Di excel sebelumnya yang harusnya cuman 2 sd 3 detik, bisa dieksekusi hingga 90 detik

Mohon pencerahannya om-om semuanya

Thanks in advance atas sharing nya

Regards
Hilman

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

Kid

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Administrator

Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

berikut coding nya...

Sub SalinDataPoultry()

Dim RgSumber As Range
Dim wsSumber As Worksheet
Dim wbSumber As Workbook

Dim rgData As Range
Dim wsData As Worksheet
Dim wbData As Workbook
Dim rwData As Long

Dim rg As Range

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.PrintCommunication = False

Set wbData = ThisWorkbook
Set wsData = wbData.Worksheets("Daily Production")

rwData = wsData.Range("A1").End(xlDown).Row

Set wbSumber = Workbooks("STOCK 1220-PA (003).xlsx") 'Ganti
Set wsSumber = wbSumber.Worksheets("MASTER")
Set RgSumber = wsSumber.Range("I75:GL75") 'Ganti

For Each rg In RgSumber


If rg.Value > 0 Then
rwData = rwData + 1
wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
wsData.Cells(rwData, 6).Value = "Crumbler"
wsData.Cells(rwData, 7).Value = rg.Value

Select Case wsSumber.Cells(4, rg.Column).Value
Case "Prod."
wsData.Cells(rwData, 5).Value = "Production"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Retur"
wsData.Cells(rwData, 5).Value = "Retur"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Sales"
wsData.Cells(rwData, 5).Value = "Sales"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Depo"
wsData.Cells(rwData, 5).Value = "Depo"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Remix"
wsData.Cells(rwData, 5).Value = "Remix"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case vbNullString
wsData.Cells(rwData, 5).Value = "Stock"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case Else
MsgBox "Header tidak ketemu"
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End Select

End If


If rg.Offset(1, 0).Value > 0 Then
rwData = rwData + 1
wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
wsData.Cells(rwData, 6).Value = "Mash"
wsData.Cells(rwData, 7).Value = rg.Offset(1, 0).Value
Select Case wsSumber.Cells(4, rg.Column).Value
Case "Prod."
wsData.Cells(rwData, 5).Value = "Production"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Retur"
wsData.Cells(rwData, 5).Value = "Retur"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Sales"
wsData.Cells(rwData, 5).Value = "Sales"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Depo"
wsData.Cells(rwData, 5).Value = "Depo"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Remix"
wsData.Cells(rwData, 5).Value = "Remix"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case vbNullString
wsData.Cells(rwData, 5).Value = "Stock"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case Else
MsgBox "Header tidak ketemu"
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End Select

End If

Next rg

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.PrintCommunication = True

End Sub

select, activate, selection dll, sudah tidak pernah saya gunakan lagi..

Regards
Hilman

On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:

Hai Hilman,

Coba hindari penggunaan Select, Activate, Selection, ActiveCells, ActiveWorkbook
hindari buka tutup proteksi sheet maupun workbook dengan memanfaatkan userinterfaceonly saat akan mem-protect, supaya VBA bisa tetap bekerja seperti biasa tanpa perlu membuka proteksi.
hindari conditional formatting pada cells dalam jumlah banyak (apalagi di cells yang masih kosong [belum ada datanya])
Coba ditelaah ulang setiap prosesnya, mungkin bisa disusun alur proses yang lebih efisien.
Coba ditelaah ulang setiap baris program di setiap prosesnya, mungkin bisa disusun ulang agar lebih sederhana.

Excel 2016 ke atas adalah era baru Excel setelah era Excel 2003. Jadi Excel 2007 s.d. Excel 2013 bisa disebut versi transisi.
Jadi, perbaikan (tuntutan untuk lebih efisien dalam proses otomasi) VBA juga diperlukan.

Regards,
Kid

On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
Selamat malam Om
Setelah hampir 5 tahun gak main-main ama makro, dan saat ini coba pakai makro lagi,
kaget banget saya dengan speed of excecution Excel (office365) yang sangat lambat dibandingkan dengan Excel 2013.

Padahal udah :

    Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Tapi tetap saja masih sangat lambat.

Di excel sebelumnya yang harusnya cuman 2 sd 3 detik, bisa dieksekusi hingga 90 detik

Mohon pencerahannya om-om semuanya

Thanks in advance atas sharing nya

Regards
Hilman

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

Hilman

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Berikut workbook yang berisi macro nya dan workbook data nya

Thanks Mr Kids....

On Thursday, April 8, 2021, 07:52:22 AM GMT+7, Mr. Kid <[hidden email]> wrote:

Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

berikut coding nya...

Sub SalinDataPoultry()

Dim RgSumber As Range
Dim wsSumber As Worksheet
Dim wbSumber As Workbook

Dim rgData As Range
Dim wsData As Worksheet
Dim wbData As Workbook
Dim rwData As Long

Dim rg As Range

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.PrintCommunication = False

Set wbData = ThisWorkbook
Set wsData = wbData.Worksheets("Daily Production")

rwData = wsData.Range("A1").End(xlDown).Row

Set wbSumber = Workbooks("STOCK 1220-PA (003).xlsx") 'Ganti
Set wsSumber = wbSumber.Worksheets("MASTER")
Set RgSumber = wsSumber.Range("I75:GL75") 'Ganti

For Each rg In RgSumber


If rg.Value > 0 Then
rwData = rwData + 1
wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
wsData.Cells(rwData, 6).Value = "Crumbler"
wsData.Cells(rwData, 7).Value = rg.Value

Select Case wsSumber.Cells(4, rg.Column).Value
Case "Prod."
wsData.Cells(rwData, 5).Value = "Production"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Retur"
wsData.Cells(rwData, 5).Value = "Retur"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Sales"
wsData.Cells(rwData, 5).Value = "Sales"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Depo"
wsData.Cells(rwData, 5).Value = "Depo"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Remix"
wsData.Cells(rwData, 5).Value = "Remix"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case vbNullString
wsData.Cells(rwData, 5).Value = "Stock"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case Else
MsgBox "Header tidak ketemu"
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End Select

End If


If rg.Offset(1, 0).Value > 0 Then
rwData = rwData + 1
wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
wsData.Cells(rwData, 6).Value = "Mash"
wsData.Cells(rwData, 7).Value = rg.Offset(1, 0).Value
Select Case wsSumber.Cells(4, rg.Column).Value
Case "Prod."
wsData.Cells(rwData, 5).Value = "Production"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Retur"
wsData.Cells(rwData, 5).Value = "Retur"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Sales"
wsData.Cells(rwData, 5).Value = "Sales"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Depo"
wsData.Cells(rwData, 5).Value = "Depo"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Remix"
wsData.Cells(rwData, 5).Value = "Remix"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case vbNullString
wsData.Cells(rwData, 5).Value = "Stock"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case Else
MsgBox "Header tidak ketemu"
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End Select

End If

Next rg

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.PrintCommunication = True

End Sub

select, activate, selection dll, sudah tidak pernah saya gunakan lagi..

Regards
Hilman

On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:

Hai Hilman,

Coba hindari penggunaan Select, Activate, Selection, ActiveCells, ActiveWorkbook
hindari buka tutup proteksi sheet maupun workbook dengan memanfaatkan userinterfaceonly saat akan mem-protect, supaya VBA bisa tetap bekerja seperti biasa tanpa perlu membuka proteksi.
hindari conditional formatting pada cells dalam jumlah banyak (apalagi di cells yang masih kosong [belum ada datanya])
Coba ditelaah ulang setiap prosesnya, mungkin bisa disusun alur proses yang lebih efisien.
Coba ditelaah ulang setiap baris program di setiap prosesnya, mungkin bisa disusun ulang agar lebih sederhana.

Excel 2016 ke atas adalah era baru Excel setelah era Excel 2003. Jadi Excel 2007 s.d. Excel 2013 bisa disebut versi transisi.
Jadi, perbaikan (tuntutan untuk lebih efisien dalam proses otomasi) VBA juga diperlukan.

Regards,
Kid

On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
Selamat malam Om
Setelah hampir 5 tahun gak main-main ama makro, dan saat ini coba pakai makro lagi,
kaget banget saya dengan speed of excecution Excel (office365) yang sangat lambat dibandingkan dengan Excel 2013.

Padahal udah :

    Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Tapi tetap saja masih sangat lambat.

Di excel sebelumnya yang harusnya cuman 2 sd 3 detik, bisa dieksekusi hingga 90 detik

Mohon pencerahannya om-om semuanya

Thanks in advance atas sharing nya

Regards
Hilman

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

Daily Report Recap Belajar Excel.xlsm (83K) Download Attachment

Data 2021-03.xlsx (187K) Download Attachment

Kid

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Administrator

Coba di Excel Options -> pane formula -> bagian error checking dikurangi yang dicentang

Kemudian, di Excel Table, formula yang pakai @ diganti dengan merujuk alamat cells nya

Kalau office 365 nya sign in di account ms, di sign out saja.

On Fri, Apr 9, 2021 at 4:53 PM Hilman <[hidden email]> wrote:

Berikut workbook yang berisi macro nya dan workbook data nya

Thanks Mr Kids....

On Thursday, April 8, 2021, 07:52:22 AM GMT+7, Mr. Kid <[hidden email]> wrote:

Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

berikut coding nya...

Sub SalinDataPoultry()

Dim RgSumber As Range
Dim wsSumber As Worksheet
Dim wbSumber As Workbook

Dim rgData As Range
Dim wsData As Worksheet
Dim wbData As Workbook
Dim rwData As Long

Dim rg As Range

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.PrintCommunication = False

Set wbData = ThisWorkbook
Set wsData = wbData.Worksheets("Daily Production")

rwData = wsData.Range("A1").End(xlDown).Row

Set wbSumber = Workbooks("STOCK 1220-PA (003).xlsx") 'Ganti
Set wsSumber = wbSumber.Worksheets("MASTER")
Set RgSumber = wsSumber.Range("I75:GL75") 'Ganti

For Each rg In RgSumber


If rg.Value > 0 Then
rwData = rwData + 1
wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
wsData.Cells(rwData, 6).Value = "Crumbler"
wsData.Cells(rwData, 7).Value = rg.Value

Select Case wsSumber.Cells(4, rg.Column).Value
Case "Prod."
wsData.Cells(rwData, 5).Value = "Production"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Retur"
wsData.Cells(rwData, 5).Value = "Retur"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Sales"
wsData.Cells(rwData, 5).Value = "Sales"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Depo"
wsData.Cells(rwData, 5).Value = "Depo"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Remix"
wsData.Cells(rwData, 5).Value = "Remix"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case vbNullString
wsData.Cells(rwData, 5).Value = "Stock"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case Else
MsgBox "Header tidak ketemu"
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End Select

End If


If rg.Offset(1, 0).Value > 0 Then
rwData = rwData + 1
wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
wsData.Cells(rwData, 6).Value = "Mash"
wsData.Cells(rwData, 7).Value = rg.Offset(1, 0).Value
Select Case wsSumber.Cells(4, rg.Column).Value
Case "Prod."
wsData.Cells(rwData, 5).Value = "Production"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Retur"
wsData.Cells(rwData, 5).Value = "Retur"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Sales"
wsData.Cells(rwData, 5).Value = "Sales"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Depo"
wsData.Cells(rwData, 5).Value = "Depo"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Remix"
wsData.Cells(rwData, 5).Value = "Remix"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case vbNullString
wsData.Cells(rwData, 5).Value = "Stock"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case Else
MsgBox "Header tidak ketemu"
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End Select

End If

Next rg

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.PrintCommunication = True

End Sub

select, activate, selection dll, sudah tidak pernah saya gunakan lagi..

Regards
Hilman

On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:

Hai Hilman,

Coba hindari penggunaan Select, Activate, Selection, ActiveCells, ActiveWorkbook
hindari buka tutup proteksi sheet maupun workbook dengan memanfaatkan userinterfaceonly saat akan mem-protect, supaya VBA bisa tetap bekerja seperti biasa tanpa perlu membuka proteksi.
hindari conditional formatting pada cells dalam jumlah banyak (apalagi di cells yang masih kosong [belum ada datanya])
Coba ditelaah ulang setiap prosesnya, mungkin bisa disusun alur proses yang lebih efisien.
Coba ditelaah ulang setiap baris program di setiap prosesnya, mungkin bisa disusun ulang agar lebih sederhana.

Excel 2016 ke atas adalah era baru Excel setelah era Excel 2003. Jadi Excel 2007 s.d. Excel 2013 bisa disebut versi transisi.
Jadi, perbaikan (tuntutan untuk lebih efisien dalam proses otomasi) VBA juga diperlukan.

Regards,
Kid

On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
Selamat malam Om
Setelah hampir 5 tahun gak main-main ama makro, dan saat ini coba pakai makro lagi,
kaget banget saya dengan speed of excecution Excel (office365) yang sangat lambat dibandingkan dengan Excel 2013.

Padahal udah :

    Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Tapi tetap saja masih sangat lambat.

Di excel sebelumnya yang harusnya cuman 2 sd 3 detik, bisa dieksekusi hingga 90 detik

Mohon pencerahannya om-om semuanya

Thanks in advance atas sharing nya

Regards
Hilman

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/C3D9E0F5-8FA7-4885-A030-56FFD67412F0%40gmail.com.

Kid

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Administrator

Biasanya office 365 bermasalah dengan hardware graphics (bisa di-disable dengan dicentang di Excel Options Display).

Protect unprotect file, workbook, sheets (termasuk lock unlock cells dalam sheet yang diprotect), set visible sheets juga.

Kalau file dibuat dengan versi sebelum Office 365, coba update office 365 (supaya fitur konversi dan compatibility-nya bisa lebih baik).

Kalau masalah compatibility, biasanya dengan membuat ulang langsung di office 365 dari new workbook bisa sering menyelesaikan masalah speed.

Bikin ulang persis, kalau copy paste, pastikan paste values lalu diformat atau dipasangi formula atau dibuat tabelnya ulang. Script VBA bisa di-copy paste apa adanya.

Kalau dengan bikin ulang masih tetap lambat, barulah dicari alur proses dalam script yang lebih efisien.

Regards,

Kid

On Fri, Apr 9, 2021 at 8:55 PM Hilman <[hidden email]> wrote:

Masih sama hasil nya....
Apanya lagi ya ?
Hehehe... btw thanks ya...

On Friday, April 9, 2021, 07:42:34 PM GMT+7, Mr. Kid <[hidden email]> wrote:

Coba di Excel Options -> pane formula -> bagian error checking dikurangi yang dicentang
Kemudian, di Excel Table, formula yang pakai @ diganti dengan merujuk alamat cells nya
Kalau office 365 nya sign in di account ms, di sign out saja.

On Fri, Apr 9, 2021 at 4:53 PM Hilman <[hidden email]> wrote:

Berikut workbook yang berisi macro nya dan workbook data nya

Thanks Mr Kids....

On Thursday, April 8, 2021, 07:52:22 AM GMT+7, Mr. Kid <[hidden email]> wrote:

Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

berikut coding nya...

Sub SalinDataPoultry()

Dim RgSumber As Range
Dim wsSumber As Worksheet
Dim wbSumber As Workbook

Dim rgData As Range
Dim wsData As Worksheet
Dim wbData As Workbook
Dim rwData As Long

Dim rg As Range

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.PrintCommunication = False

Set wbData = ThisWorkbook
Set wsData = wbData.Worksheets("Daily Production")

rwData = wsData.Range("A1").End(xlDown).Row

Set wbSumber = Workbooks("STOCK 1220-PA (003).xlsx") 'Ganti
Set wsSumber = wbSumber.Worksheets("MASTER")
Set RgSumber = wsSumber.Range("I75:GL75") 'Ganti

For Each rg In RgSumber


If rg.Value > 0 Then
rwData = rwData + 1
wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
wsData.Cells(rwData, 6).Value = "Crumbler"
wsData.Cells(rwData, 7).Value = rg.Value

Select Case wsSumber.Cells(4, rg.Column).Value
Case "Prod."
wsData.Cells(rwData, 5).Value = "Production"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Retur"
wsData.Cells(rwData, 5).Value = "Retur"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Sales"
wsData.Cells(rwData, 5).Value = "Sales"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Depo"
wsData.Cells(rwData, 5).Value = "Depo"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Remix"
wsData.Cells(rwData, 5).Value = "Remix"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case vbNullString
wsData.Cells(rwData, 5).Value = "Stock"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case Else
MsgBox "Header tidak ketemu"
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End Select

End If


If rg.Offset(1, 0).Value > 0 Then
rwData = rwData + 1
wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
wsData.Cells(rwData, 6).Value = "Mash"
wsData.Cells(rwData, 7).Value = rg.Offset(1, 0).Value
Select Case wsSumber.Cells(4, rg.Column).Value
Case "Prod."
wsData.Cells(rwData, 5).Value = "Production"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Retur"
wsData.Cells(rwData, 5).Value = "Retur"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Sales"
wsData.Cells(rwData, 5).Value = "Sales"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Depo"
wsData.Cells(rwData, 5).Value = "Depo"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Remix"
wsData.Cells(rwData, 5).Value = "Remix"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case vbNullString
wsData.Cells(rwData, 5).Value = "Stock"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case Else
MsgBox "Header tidak ketemu"
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End Select

End If

Next rg

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.PrintCommunication = True

End Sub

select, activate, selection dll, sudah tidak pernah saya gunakan lagi..

Regards
Hilman

On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:

Hai Hilman,

Coba hindari penggunaan Select, Activate, Selection, ActiveCells, ActiveWorkbook
hindari buka tutup proteksi sheet maupun workbook dengan memanfaatkan userinterfaceonly saat akan mem-protect, supaya VBA bisa tetap bekerja seperti biasa tanpa perlu membuka proteksi.
hindari conditional formatting pada cells dalam jumlah banyak (apalagi di cells yang masih kosong [belum ada datanya])
Coba ditelaah ulang setiap prosesnya, mungkin bisa disusun alur proses yang lebih efisien.
Coba ditelaah ulang setiap baris program di setiap prosesnya, mungkin bisa disusun ulang agar lebih sederhana.

Excel 2016 ke atas adalah era baru Excel setelah era Excel 2003. Jadi Excel 2007 s.d. Excel 2013 bisa disebut versi transisi.
Jadi, perbaikan (tuntutan untuk lebih efisien dalam proses otomasi) VBA juga diperlukan.

Regards,
Kid

On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
Selamat malam Om
Setelah hampir 5 tahun gak main-main ama makro, dan saat ini coba pakai makro lagi,
kaget banget saya dengan speed of excecution Excel (office365) yang sangat lambat dibandingkan dengan Excel 2013.

Padahal udah :

    Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Tapi tetap saja masih sangat lambat.

Di excel sebelumnya yang harusnya cuman 2 sd 3 detik, bisa dieksekusi hingga 90 detik

Mohon pencerahannya om-om semuanya

Thanks in advance atas sharing nya

Regards
Hilman

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/C3D9E0F5-8FA7-4885-A030-56FFD67412F0%40gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPs1JcObqU45JqHdATgucb%2BhLcx--uyhJ3Op%3DvPR4UhW3A%40mail.gmail.com.

Kid

Re: [milis-belajar-excel] Macro pada Excel (Office 365) sangat lambat

Administrator

Excel versi yang lebih baru memang lebih lambat di-vba-nya, umumnya lebih lambat sekitar 2-3 kali ketika ada proses menulis ke cells satu per satu.

On Sat, Apr 10, 2021 at 8:58 AM Hilman <[hidden email]> wrote:

Saya coba dengan buat code yang sederhana, yakni mengisi cell A1:A10000, seperti di bawah ini :

Sub TestMacro()

Dim cntTime As Double
Dim ws As Worksheet
Dim rg As Range

cntTime = Timer

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.PrintCommunication = False

Set ws = ThisWorkbook.Worksheets("sheet1")

For Each rg In ws.Range("A1:A10000")
rg.Value = "coba" & rg.Row
Next rg

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.PrintCommunication = True

cntTime = Timer - cntTime

MsgBox cntTime

Debug.Print cntTime

End Sub

Waktu proses nya 4 detik.
Kalo dicoba di excel 2013, ini gak nyampe 0.1 detik

Ini saya buat ulang di excel 2016....

Regards
Hilman

On Saturday, April 10, 2021, 08:26:34 AM GMT+7, Mr. Kid <[hidden email]> wrote:

Biasanya office 365 bermasalah dengan hardware graphics (bisa di-disable dengan dicentang di Excel Options Display).
Protect unprotect file, workbook, sheets (termasuk lock unlock cells dalam sheet yang diprotect), set visible sheets juga.
Kalau file dibuat dengan versi sebelum Office 365, coba update office 365 (supaya fitur konversi dan compatibility-nya bisa lebih baik).

Kalau masalah compatibility, biasanya dengan membuat ulang langsung di office 365 dari new workbook bisa sering menyelesaikan masalah speed.
Bikin ulang persis, kalau copy paste, pastikan paste values lalu diformat atau dipasangi formula atau dibuat tabelnya ulang. Script VBA bisa di-copy paste apa adanya.

Kalau dengan bikin ulang masih tetap lambat, barulah dicari alur proses dalam script yang lebih efisien.

Regards,
Kid

On Fri, Apr 9, 2021 at 8:55 PM Hilman <[hidden email]> wrote:

Masih sama hasil nya....
Apanya lagi ya ?
Hehehe... btw thanks ya...

On Friday, April 9, 2021, 07:42:34 PM GMT+7, Mr. Kid <[hidden email]> wrote:

Coba di Excel Options -> pane formula -> bagian error checking dikurangi yang dicentang
Kemudian, di Excel Table, formula yang pakai @ diganti dengan merujuk alamat cells nya
Kalau office 365 nya sign in di account ms, di sign out saja.

On Fri, Apr 9, 2021 at 4:53 PM Hilman <[hidden email]> wrote:

Berikut workbook yang berisi macro nya dan workbook data nya

Thanks Mr Kids....

On Thursday, April 8, 2021, 07:52:22 AM GMT+7, Mr. Kid <[hidden email]> wrote:

Boleh share workbook sample berisi dummy data ?

Sent from my smart enough phone

On Apr 6, 2021, at 14:37, Hilman <[hidden email]> wrote:

berikut coding nya...

Sub SalinDataPoultry()

Dim RgSumber As Range
Dim wsSumber As Worksheet
Dim wbSumber As Workbook

Dim rgData As Range
Dim wsData As Worksheet
Dim wbData As Workbook
Dim rwData As Long

Dim rg As Range

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.PrintCommunication = False

Set wbData = ThisWorkbook
Set wsData = wbData.Worksheets("Daily Production")

rwData = wsData.Range("A1").End(xlDown).Row

Set wbSumber = Workbooks("STOCK 1220-PA (003).xlsx") 'Ganti
Set wsSumber = wbSumber.Worksheets("MASTER")
Set RgSumber = wsSumber.Range("I75:GL75") 'Ganti

For Each rg In RgSumber


If rg.Value > 0 Then
rwData = rwData + 1
wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
wsData.Cells(rwData, 6).Value = "Crumbler"
wsData.Cells(rwData, 7).Value = rg.Value

Select Case wsSumber.Cells(4, rg.Column).Value
Case "Prod."
wsData.Cells(rwData, 5).Value = "Production"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Retur"
wsData.Cells(rwData, 5).Value = "Retur"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Sales"
wsData.Cells(rwData, 5).Value = "Sales"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Depo"
wsData.Cells(rwData, 5).Value = "Depo"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Remix"
wsData.Cells(rwData, 5).Value = "Remix"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case vbNullString
wsData.Cells(rwData, 5).Value = "Stock"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case Else
MsgBox "Header tidak ketemu"
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End Select

End If


If rg.Offset(1, 0).Value > 0 Then
rwData = rwData + 1
wsData.Cells(rwData, 4).Value = "Poultry Old Tower"
wsData.Cells(rwData, 6).Value = "Mash"
wsData.Cells(rwData, 7).Value = rg.Offset(1, 0).Value
Select Case wsSumber.Cells(4, rg.Column).Value
Case "Prod."
wsData.Cells(rwData, 5).Value = "Production"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Retur"
wsData.Cells(rwData, 5).Value = "Retur"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Sales"
wsData.Cells(rwData, 5).Value = "Sales"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Depo"
wsData.Cells(rwData, 5).Value = "Depo"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case "Remix"
wsData.Cells(rwData, 5).Value = "Remix"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case vbNullString
wsData.Cells(rwData, 5).Value = "Stock"
wsData.Cells(rwData, 1).Value = wsSumber.Cells(2, rg.Column).MergeArea.Cells(1, 1).Value
Case Else
MsgBox "Header tidak ketemu"
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Exit Sub
End Select

End If

Next rg

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.PrintCommunication = True

End Sub

select, activate, selection dll, sudah tidak pernah saya gunakan lagi..

Regards
Hilman

On Tuesday, April 6, 2021, 12:50:11 PM GMT+7, Mr. Kid <[hidden email]> wrote:

Hai Hilman,

Coba hindari penggunaan Select, Activate, Selection, ActiveCells, ActiveWorkbook
hindari buka tutup proteksi sheet maupun workbook dengan memanfaatkan userinterfaceonly saat akan mem-protect, supaya VBA bisa tetap bekerja seperti biasa tanpa perlu membuka proteksi.
hindari conditional formatting pada cells dalam jumlah banyak (apalagi di cells yang masih kosong [belum ada datanya])
Coba ditelaah ulang setiap prosesnya, mungkin bisa disusun alur proses yang lebih efisien.
Coba ditelaah ulang setiap baris program di setiap prosesnya, mungkin bisa disusun ulang agar lebih sederhana.

Excel 2016 ke atas adalah era baru Excel setelah era Excel 2003. Jadi Excel 2007 s.d. Excel 2013 bisa disebut versi transisi.
Jadi, perbaikan (tuntutan untuk lebih efisien dalam proses otomasi) VBA juga diperlukan.

Regards,
Kid

On Tue, Apr 6, 2021 at 1:44 AM 'Hilman' via milis Belajar-Excel <[hidden email]> wrote:
Selamat malam Om
Setelah hampir 5 tahun gak main-main ama makro, dan saat ini coba pakai makro lagi,
kaget banget saya dengan speed of excecution Excel (office365) yang sangat lambat dibandingkan dengan Excel 2013.

Padahal udah :

    Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Tapi tetap saja masih sangat lambat.

Di excel sebelumnya yang harusnya cuman 2 sd 3 detik, bisa dieksekusi hingga 90 detik

Mohon pencerahannya om-om semuanya

Thanks in advance atas sharing nya

Regards
Hilman

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/1535188280.604216.1617637302393%40mail.yahoo.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPt0NoiLe6cjB_JyeFrTdQ3FiBBrDmxoWzFJg7VCE31F7A%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/C3D9E0F5-8FA7-4885-A030-56FFD67412F0%40gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPs1JcObqU45JqHdATgucb%2BhLcx--uyhJ3Op%3DvPR4UhW3A%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "milis Belajar-Excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/milis-belajar-excel/CAOiZkPu8zJKv8CbJmXXX1zopPD3RbG8e%2Befb7sS_kb%2BSCgub5g%40mail.gmail.com.