VBA Macro to Update Graphs and Copy Values Between Sheets

Question

I have quite a large Excel sheet which uses a VBA macro to update and copy values from one sheet into another. I'm looking to optimize the script such that it takes less time to run. The purpose of the macro is to allow for saving historical data while being able to update the value on a display page (as in: historical data on one sheet and current data on another). Historical data is stored in "Daily Avgs (year)' while current data is in "Chem Cost." I'm not very familiar with VBA, but the script I wrote currently works but is horribly unoptimized. Just looking for tips on how I could make this better. Below is a snippet of the script, and is continued for a cumulative trend where the x-axis range is defined.

Thank you.

 Sub Daily()
Dim X As Integer
X = WorksheetFunction.Lookup(Range("Look_up_day"), Worksheets("Daily Avgs (year)").Rows("3:3"), Worksheets("Daily Avgs (year)").Rows("2:2"))
Sheets("Chem Cost").Range("ACPT").Copy
Sheets("Daily Avgs (year)").Cells(4, X).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("Chem Cost").Range("Grade1").Copy
Sheets("Daily Avgs (year)").Cells(18, X).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("Chem Cost").Range("Grade2").Copy
Sheets("Daily Avgs (year)").Cells(30, X).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("Chem Cost").Range("Grade3").Copy
Sheets("Daily Avgs (year)").Cells(42, X).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'average cost per thousand squared ft
Sheets("Chem Cost").Range("ACPMSF").Copy
Sheets("Daily Avgs (year)").Cells(91, X).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'x_axis range set up for cumulative trend ($/ton)
 'chemA
 Sheets("Chem Cost").ChartObjects("Chart 11").Chart.Axes(xlCategory).MinimumScale = Sheets("Daily Avgs (year)").Range("D1").Value
 Sheets("Chem Cost").ChartObjects("Chart 11").Chart.Axes(xlCategory).MaximumScale = Sheets("Daily Avgs (year)").Range("G1").Value
 'chemB
 Sheets("Chem Cost").ChartObjects("Chart 20").Chart.Axes(xlCategory).MinimumScale = Sheets("Daily Avgs (year)").Range("D1").Value
 Sheets("Chem Cost").ChartObjects("Chart 20").Chart.Axes(xlCategory).MaximumScale = Sheets("Daily Avgs (year)").Range("G1").Value
 'chemC
 Sheets("Chem Cost").ChartObjects("Chart 21").Chart.Axes(xlCategory).MinimumScale = Sheets("Daily Avgs (year)").Range("D1").Value
 Sheets("Chem Cost").ChartObjects("Chart 21").Chart.Axes(xlCategory).MaximumScale = Sheets("Daily Avgs (year)").Range("G1").Value
 'chemD
'...repeated for 40 charts.
    End Sub

pgSystemTester · Answer

This isn't a proper question as there is no way to directly provide a solution, however the obvious thing that probably jumps out to everyone is your usage of Select along with Copy Paste Value. Both of these create all kinds of activities that are unnecassary. Instead, just set the values.

Example:

Sheets("Daily Avgs (year)").Cells(4, X).value = Sheets("Chem Cost").Range("ACPT").value

Also, probably better to use Long instead of integer in case your data grows beyond 32k rows and because it's better.

Other standard techniques for improving speed are out there if you google how to speed up my macro (Turn off calls, events, screenupdating, etc.)

VBA Macro to Update Graphs and Copy Values Between Sheets

1 Answers