Check the VBA code efficiency and speed.

Question

I am working on Excel 2013 and I am a novice in vba coding, I have several files into which I have to copy data into.

So at a given point of time, I have the source file (from which the data is copied) and the destination file (to which the data is copied)

Both workbooks open would have same sheetnames. Some sheets are also created in the below vba codeSomehow I feel the below code is not efficient as its taking time to execute and complete its task. Any suggestion would be of great help

Sub COPYING_GSTv3()

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False


'Remove password from respective sheets
'---------------------------------------------------
ThisWorkbook.Activate
ActiveWindow.ActivateNext 'toggles the window
Sheets("Budget").Unprotect "bhasad"
Sheets("ET Working").Unprotect "bhasad18"
Sheets("Occupancy").Unprotect "bhasad"
Sheets("Refuel").Unprotect "bhasad"
Sheets("Property Rent").Unprotect "bhasad18"
'---------------------------------------------------

'Replication of "Budget, ET working, Occupancy, Refuel and Property Rent" sheets
'---------------------------------------------------
Sheets("Budget").Range("n63").Copy
Sheets("budget").Range("n65").PasteSpecial xlPasteValues
Sheets("Budget").Range("n1").Formula = "=n63-n65"

Sheets("BUDGET").Copy Before:=Sheets(4)
Sheets("BUDGET (2)").Name = "BUDGET (GST)"
Sheets("ET Working").Copy Before:=Sheets(6)
Sheets("ET Working (2)").Name = "ET Working (GST)"
Sheets("Occupancy").Copy Before:=Sheets(8)
Sheets("Occupancy (2)").Name = "Occupancy (GST)"
Sheets("Property Rent").Copy Before:=Sheets(17)
Sheets("Property Rent (2)").Name = "Property Rent (GST)"

'---------------------------------------------------
'Color sheets
'---------------------------------------------------
With ActiveWorkbook.Sheets("BUDGET (GST)").Tab
        .Color = 255
        .TintAndShade = 0
End With
With ActiveWorkbook.Sheets("ET Working (GST)").Tab
        .Color = 255
        .TintAndShade = 0
End With
With ActiveWorkbook.Sheets("Occupancy (GST)").Tab
        .Color = 255
        .TintAndShade = 0
End With

With ActiveWorkbook.Sheets("Property Rent (GST)").Tab
        .Color = 255
        .TintAndShade = 0
End With

'---------------------------------------------------

'**pasting data from Budget with GST sheet to repective budget workbook open
'---------------------------------------------------

ThisWorkbook.Activate
Sheets("BUDGET (GST)").Select
Range("A212:m213").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("BUDGET (GST)").Select
Range("A212").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("BUDGET (GST)").Select
Range("b94:m96").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("BUDGET (GST)").Select
Range("b94").Select
ActiveSheet.Paste

Sheets("BUDGET (GST)").Select
Range("b109:m110").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("BUDGET (GST)").Select
Range("b109").Select
ActiveSheet.Paste


ThisWorkbook.Activate
Sheets("BUDGET (GST)").Select
Range("b128:m132").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("BUDGET (GST)").Select
Range("b128").Select
ActiveSheet.Paste


ThisWorkbook.Activate
Sheets("BUDGET (GST)").Select
Range("b162:m162").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("BUDGET (GST)").Select
Range("b162").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("BUDGET (GST)").Select
Range("b179:m180").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("BUDGET (GST)").Select
Range("b179").Select
ActiveSheet.Paste
'Removing source file name
Cells.Replace What:="[Budget With GST v3.xlsb]", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False



ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("A2:a35").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("A2").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("d9").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("d9").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c16:n17").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c16").Select
ActiveSheet.Paste


ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c24:n24").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c24").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c53:n53").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c53").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c69:n69").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c69").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c71:n71").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c71").Select
ActiveSheet.Paste


ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c73:n73").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c73").Select
ActiveSheet.Paste


ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c78:n78").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c78").Select
ActiveSheet.Paste


ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c80:n80").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c80").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c82:n82").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c82").Select
ActiveSheet.Paste


ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c88:n88").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c88").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c90:n92").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c90").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c97:n97").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c97").Select
ActiveSheet.Paste


ThisWorkbook.Activate
Sheets("ET Working (GST)").Select
Range("c99:n101").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("ET Working (GST)").Select
Range("c99").Select
ActiveSheet.Paste
'Removing source file name
Cells.Replace What:="[Budget With GST v3.xlsb]", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

ThisWorkbook.Activate
Sheets("Occupancy (GST)").Select
Range("d27:p27").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Occupancy (GST)").Select
Range("d27").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("Occupancy (GST)").Select
Range("d30:p30").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Occupancy (GST)").Select
Range("d30").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("Occupancy (GST)").Select
Range("d32:p32").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Occupancy (GST)").Select
Range("d32").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("Occupancy (GST)").Select
Range("d37:p38").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Occupancy (GST)").Select
Range("d37").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("Occupancy (GST)").Select
Range("d55:p56").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Occupancy (GST)").Select
Range("d55").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("Occupancy (GST)").Select
Range("d63:p63").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Occupancy (GST)").Select
Range("d63").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("Occupancy (GST)").Select
Range("d70:p70").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Occupancy (GST)").Select
Range("d70").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("Occupancy (GST)").Select
Range("d74:p74").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Occupancy (GST)").Select
Range("d74").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("Occupancy (GST)").Select
Range("d76:p76").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Occupancy (GST)").Select
Range("d76").Select
ActiveSheet.Paste
'Removing source file name
Cells.Replace What:="[Budget With GST v3.xlsb]", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

ThisWorkbook.Activate
Sheets("Refuel").Select
Range("e1").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Refuel").Select
Range("e1").Select
ActiveSheet.Paste



ThisWorkbook.Activate
Sheets("Refuel").Select
Range("a299:m303").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Refuel").Select
Range("a299").Select
ActiveSheet.Paste
'Removing source file name
Cells.Replace What:="[Budget With GST v3.xlsb]", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


ThisWorkbook.Activate
Sheets("Property Rent (GST)").Select
Range("b10:m16").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Property Rent (GST)").Select
Range("b10").Select
ActiveSheet.Paste


ThisWorkbook.Activate
Sheets("Property Rent (GST)").Select
Range("b26:m31").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Property Rent (GST)").Select
Range("b26").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("Property Rent (GST)").Select
Range("b37:m37").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Property Rent (GST)").Select
Range("b37").Select
ActiveSheet.Paste

ThisWorkbook.Activate
Sheets("Property Rent (GST)").Select
Range("b43:m43").Select
Selection.Copy
ActiveWindow.ActivateNext 'toggles the window
Sheets("Property Rent (GST)").Select
Range("b43").Select
ActiveSheet.Paste
'Removing source file name
Cells.Replace What:="[Budget With GST v3.xlsb]", Replacement:="", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False





'---------------------------------------------------

'Differential of Budget and Budget (GST)
Sheets("Budget (GST)").Select

Columns("O:O").Select
    Selection.ClearFormats
    Range("O10").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-BUDGET!RC[-1]"
    Columns("O:O").EntireColumn.AutoFit
    Selection.AutoFill Destination:=Range("O10:O201"), Type:=xlFillDefault
    Range("O10:O201").Select
    Range("O19:O20").Select
    Selection.NumberFormat = "0%"
    Columns("O:O").ColumnWidth = 7.43
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"
    ActiveWindow.SmallScroll Down:=75
    Range("O94").Select
    Selection.NumberFormat = "0%"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"
    Range("O96").Select
    ActiveWindow.SmallScroll Down:=36
    Range("O128").Select
    Selection.NumberFormat = "0%"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"
    Range("O131").Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"
    ActiveWindow.SmallScroll Down:=63



'---------------------------------------------------


'Add password from respective orginal sheets
'---------------------------------------------------
ThisWorkbook.Activate
ActiveWindow.ActivateNext 'toggles the window
Sheets("Budget").Protect "bhasad"
Sheets("ET Working").Protect "bhasad18"
Sheets("Occupancy").Protect "bhasad"
Sheets("Refuel").Protect "bhasad"
Sheets("Property Rent").Protect "bhasad18"
Sheets("Budget").Select
ThisWorkbook.Activate
Sheets("Budget").Select
'---------------------------------------------------

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.EnableEvents = True




End Sub

Start here: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros?rq=1 — Tim Williams, Mar 16 '17 at 06:14
If you have working code which merely needs improvements then you're probably in the wrong place with this post. [Code Review](http://codereview.stackexchange.com/) is where they handle existing / working code and do their upmost to improve it in terms of speed, security, sustainability, and longevity including best-practices. Give it a try. They're good! — Ralph, Mar 16 '17 at 08:43

Check the VBA code efficiency and speed.

0 Answers0