I have a reporting worksheet that gets populated with a day's events, and generates statistics based on those entries. Each day the report is duplicated and formatted ready to be populated with the new day's events. The statistics from the previous day's report need to be copied and pasted from a the previous day's "Total" cells to the new day's reports "previous" cells to create a running total. This would be a simple task, but unfortunately, the location of these cells are dependant on how many entries (Rows) have been added.

I have managed to write a very janky code that locates the required cells, copies them and pastes them into the correct new cells. My current code moves each cell's value individually and although it works, it is very inefficient.

I my first attempt at this code was "more tidy", but it fails at the last hurdle, as it just references the cell, rather than the value (cell position) it contains.

**My inefficient working code is:**

VBA Code:

```
'Find Reference cell
Range("AD1").Formula = "=MATCH(""STOP_HERE"",C:C,0)" 'This formula finds the Reference cell (gray row)
'Copy Mob&Demob hours to date
Range("AE1").Formula = "= AD1 +7"
Range("AF1").Formula = "= AD1 +8"
Range("AG1").Formula = "= AD1 +9"
Range("AH1").Formula = "= AD1 +10"
Range("AI1").Formula = "= AD1 +11"
Range("AJ1").Formula = "= AD1 +12"
Dim var1 As Integer
Dim var2 As Integer
Dim var3 As Integer
Dim var4 As Integer
Dim var5 As Integer
Dim var6 As Integer
var1 = Range("AE1").Value
var2 = Range("AF1").Value
var3 = Range("AG1").Value
var4 = Range("AH1").Value
var5 = Range("AI1").Value
var6 = Range("AJ1").Value
'Paste Mob&Demob hours to date, to previous hours
Cells(var1, 9).Copy
Cells(var1, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells(var2, 9).Copy
Cells(var2, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells(var3, 9).Copy
Cells(var3, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells(var4, 9).Copy
Cells(var4, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells(var5, 9).Copy
Cells(var5, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells(var6, 9).Copy
Cells(var6, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
'Create start and end cell positions for copy
Range("AK1").Formula = "= AD1 +14"
Range("AL1").Formula = "= AD1 +15"
Range("AM1").Formula = "= AD1 +16"
Range("AN1").Formula = "= AD1 +17"
Range("AO1").Formula = "= AD1 +18"
Range("AP1").Formula = "= AD1 +19"
Dim var7 As Integer
Dim var8 As Integer
Dim var9 As Integer
Dim var10 As Integer
Dim var11 As Integer
Dim var12 As Integer
var7 = Range("AK1").Value
var8 = Range("AL1").Value
var9 = Range("AM1").Value
var10 = Range("AN1").Value
var11 = Range("AO1").Value
var12 = Range("AP1").Value
'Paste survey hours to date, to previous hours
Cells(var7, 9).Copy
Cells(var7, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells(var8, 9).Copy
Cells(var8, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells(var9, 9).Copy
Cells(var9, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells(var10, 9).Copy
Cells(var10, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells(var11, 9).Copy
Cells(var11, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
Cells(var12, 9).Copy
Cells(var12, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues
```

**and the "more tidy" but non working code is:**

VBA Code:

```
'Find Reference cell
Range("AD1").Formula = "=MATCH(""STOP_HERE"",C:C,0)" 'This formula finds the Reference cell (gray row)
'Mob&Demob hours
'Create start and end cell positions for copy
Range("AE1").Formula = "=""I"" & AD1 +7"
Range("AF1").Formula = "=""I"" & AD1 +7+(5)" 'Adjust value in brackets for total # Mob and Demob codes -1
Range("AG1").Formula = "=""F"" & AD1 +7"
'Copy Mob&Demob hours to date
Range("AE1" & ":" & "AF1").Copy
'Paste Mob&Demob hours to date, to previous hours
Range("AG1").Select
Selection.PasteSpecial Paste:=xlPasteValues
'Clear sheet naming formula
Range("AE1:AG1").ClearContents
'Survey hours
'Create start and end cell positions for copy
Range("AE1").Formula = "=""I"" & AD1 +14"
Range("AF1").Formula = "=""I"" & AD1 +14+(5)" 'Adjust value in brackets for total # Survey codes -1
Range("AG1").Formula = "=""F"" & AD1 +14"
'Copy survey hours to date
Range("AE1" & ":" & "AF1").Copy
'Paste survey hours to date, to previous hours
Range("AG1").Select
Selection.PasteSpecial Paste:=xlPasteValues
```

Any advice on how to tidy this mess up would be greatly appreciated.

Thank you in advance,

Charles