Update Graphs and Copy Values Between Sheets

Question

I have a large Excel sheet which uses VBA code 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.
Historical data is stored in "Daily Avgs (year)" while current data is in "Chem Cost".

Below is a snippet and is continued for a cumulative trend where the x-axis range is defined.

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

Start by [not using Select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Sep 07 '22 at 15:05

score 0 · Answer 1 · answered Sep 07 '22 at 15:34

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.)

Update Graphs and Copy Values Between Sheets

1 Answers1