The method for using a cell value for a variable cell position - VBA

[Charles_P]

Preface: I am very new to VBA, so try not to laugh at my code too much.

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:

'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:

'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

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your previous day's and new day's sheets. The new day's sheet should show your desired result. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Charles_P]

Apologies, I will attempt to do this asap. However MS Office has decided to "protect" my workbook so I am currently unable to use my macro.

 

[RoryA]

It looks like you want:

                Range(Range("AE1").value & ":" & Range("AF1").Value).Copy
                      'Paste Mob&Demob hours to date, to previous hours
                          Range(Range("AG1").value).PasteSpecial Paste:=xlPasteValues

and similar for the other operations.

 

[bebo021999]

Its hard to code, without visual help.
Anyway, try to go with what you have wrote in original code:

Option Explicit
Sub test()
Dim pos&, CopyRange As Range
pos = Evaluate("=MATCH(""STOP_HERE"",C:C,0)") ' pos = match position found
Set CopyRange = Cells(pos, "C").Offset(7, 6).Resize(13, 1) ' move down 7 rows, to right 6 columns, then resize down 13 rows = column I
CopyRange.Copy
CopyRange.Offset(0, -3).Select ' column I copy to similar range, but move to the left 3 columns = column F
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Charles_P]

It looks like you want:

                Range(Range("AE1").value & ":" & Range("AF1").Value).Copy
                      'Paste Mob&Demob hours to date, to previous hours
                          Range(Range("AG1").value).PasteSpecial Paste:=xlPasteValues

and similar for the other operations.

That was exactly what was needed! thank you very much