VBA is the best way to copy data from a private book

Question

I am an intern at an industrial company in Brazil, and it happens that I use excel a lot. I just started playing with VBA a couple of days ago, and I'm surprised at the many things he can do for me.

I do not have a strong programming background, so I mainly study it. The code works fine, and it takes less than 15 seconds from start to finish. I'm not worried about time, but if it could be improved it would be great.

My main goal is to keep the code simple and efficient. I will leave the company in the coming months, and I would like it to be easy to use and use. What I ask is the best way to write my code so that others can more easily understand, and if possible (of course it is!), It takes less time.

My code deletes 4 sheets of content in my current workbook, and then copies the updated data from 4 other closed books. Then close everything. :) Daily production data, and their names in Portuguese, sorry for that.

Sub CopiarBase() ' ' Atalho do teclado: Ctrl+q ' ' Variables Dim MyCurrentWB As Workbook Dim BMalharia As Worksheet Dim BBeneficiamento As Worksheet Dim BEmbalagem As Worksheet Dim BDikla As Worksheet Set MyCurrentWB = ThisWorkbook Set BMalharia = MyCurrentWB.Worksheets("B-Malharia") Set BBeneficiamento = MyCurrentWB.Worksheets("B-Beneficiamento") Set BEmbalagem = MyCurrentWB.Worksheets("B-Embalagem") Set BDikla = MyCurrentWB.Worksheets("B-Dikla") 'Clean all the cells - Workbook 1 Dim Malharia_rng As Range Set Malharia_rng = BMalharia.Range("A2:CN" & BMalharia.Cells(Rows.Count, 1).End(xlUp).Row) Malharia_rng.ClearContents Dim Ben_rng As Range Set Ben_rng = BBeneficiamento.Range("A2:CY" & BBeneficiamento.Cells(Rows.Count, 1).End(xlUp).Row) Ben_rng.ClearContents Dim Emb_rng As Range Set Emb_rng = BEmbalagem.Range("A2:CT" & BEmbalagem.Cells(Rows.Count, 1).End(xlUp).Row) Emb_rng.ClearContents Dim Dikla_rng As Range Set Dikla_rng = BDikla.Range("A2:AV" & BDikla.Cells(Rows.Count, 1).End(xlUp).Row) Dikla_rng.ClearContents 'Copy from Malharia Workbook Workbooks.Open "C:\Users\marco.henrique\Desktop\Bases\Malharia Base.xls" LastRowMB = Workbooks("Malharia Base.xls").Worksheets("Malharia Base").Cells(Rows.Count, 1).End(xlUp).Row Dim Malha_base As Range Set Malha_base = Workbooks("Malharia Base.xls").Worksheets("Malharia Base").Range("A2:CN" & LastRowMB) MyCurrentWB.Worksheets("B-Malharia").Range("A2:CN" & LastRowMB).Value = Malha_base.Value Workbooks("Malharia Base.xls").Close 'Copy from Beneficiamento Workbook Workbooks.Open "C:\Users\marco.henrique\Desktop\Bases\Beneficiamento Base.xls" LastRowBB = Workbooks("Beneficiamento Base.xls").Worksheets("Beneficiamento Base").Cells(Rows.Count, 1).End(xlUp).Row Dim Ben_base As Range Set Ben_base = Workbooks("Beneficiamento Base.xls").Worksheets("Beneficiamento Base").Range("A2:CY" & LastRowBB) MyCurrentWB.Worksheets("B-Beneficiamento").Range("A2:CY" & LastRowBB).Value = Ben_base.Value Workbooks("Beneficiamento Base.xls").Close 'Copy from Embalagem Workbook Workbooks.Open "C:\Users\marco.henrique\Desktop\Bases\Embalagem Base.xls" LastRowEB = Workbooks("Embalagem Base.xls").Worksheets("Embalagem Base").Cells(Rows.Count, 1).End(xlUp).Row Dim Emb_base As Range Set Emb_base = Workbooks("Embalagem Base.xls").Worksheets("Embalagem Base").Range("A2:CT" & LastRowEB) MyCurrentWB.Worksheets("B-Embalagem").Range("A2:CT" & LastRowEB).Value = Emb_base.Value Workbooks("Embalagem Base.xls").Close 'Copy from Dikla Workbook Workbooks.Open "C:\Users\marco.henrique\Desktop\Bases\Diklatex Base.xls" LastRowDB = Workbooks("Diklatex Base.xls").Worksheets("Diklatex Base").Cells(Rows.Count, 1).End(xlUp).Row Dim Dikla_base As Range Set Dikla_base = Workbooks("Diklatex Base.xls").Worksheets("Diklatex Base").Range("A2:AV" & LastRowDB) MyCurrentWB.Worksheets("B-Dikla").Range("A2:AV" & LastRowDB).Value = Dikla_base.Value Workbooks("Diklatex Base.xls").Close End Sub 

I apologize if I was not clear enough, of course English is not my native language. Any doubts about my code or the whole idea feel free to ask questions.

Thanks in advance for your help!

Answer 1

I usually go on to refresh the screen, interact and calculate before doing anything with the book, and then return to the previous state at the end.

 Dim oldInteractive As Boolean = Application.Interactive Dim oldCalulation As XlCalculation = Application.Calculation Dim oldScreenUpdating As Boolean = Application.ScreenUpdating Application.Interactive = False Application.Calculation = XlCalculation.xlCalculationManual Application.ScreenUpdating = False 'Your code here Application.Interactive = oldInteractive Application.Calculation = oldCalulation Application.ScreenUpdating = oldScreenUpdating 

This will prevent the calculations from running while your code is running, which may slow down your work. It is important to change Application.Calculation back to this old value, as it will remain the way you set it, even after your code has finished, which can cause confusion.

Answer 2

I'm not sure how much time you spare, but I would suggest disabling screen refresh when running the macro by adding

 Application.ScreenUpdating = False 

at the beginning of sub (and obviously the same line with = True at the end)