Cell reference, resize, offset, copy and paste

[myespooir]

Hi, I am trying to make a Macro to add trackers after clicking a button. My excel is set up this way: - The 1stColumn of Row 9 is a formula that shows today's date - Row 10 has 6 Column with 6 criteria - From Row 11 to Row 2010, they are formulas for the 6 criteria on Row 10 I am trying to make the Macro do 1) Find the last cell with data on Row 9, use the resize function to Copy whatever i have from the 1st Column of Row 9 to the 5th Column of Row 2010, and paste it to the 7th Column of Row 9 till the 12th Column of Row 2010 2) Copy whatever i have from the 1st Column of Row 9 to the 5th Column of Row 2010, and paste it at the same place with values only The goal is that this Macro can be reused everytime users hit the button. However, error messages keep popping up. Could you help me with this? Please find my macro below. Sub AddTrackers() Dim lastColumn As Long Dim r As Integer Dim a As Integer Dim b As Integer Dim c As Integer r = 9 a = 6 b = 2002 c = 5 lastColumn = ActiveSheet.Cells(r, Columns.Count).End(xlToLeft).Column Cells("r, lastColumn").Resize("r+b,lastColumn+a").Copy .Paste Destination:=Cells("r, lastColumn").Offset(, "lastColumn+c") Cells("r, lastColumn").PasteSpecial xlPasteValues Cells("r, lastColumn").Select End Sub

 

[myespooir]

Sorry, for some reason, my vba format is off.
Sub AddTrackers()

Dim lastColumn As Long
Dim r As Integer
Dim a As Integer
Dim b As Integer
Dim c As Integer


r = 9
a = 6
b = 2002
c = 5


lastColumn = ActiveSheet.Cells(r, Columns.Count).End(xlToLeft).Column


Cells("r, lastColumn").Resize("r+b,lastColumn+a").Copy

.Paste Destination:=Cells("r, lastColumn").Offset(, "lastColumn+c")

Cells("r, lastColumn").PasteSpecial xlPasteValues

Cells("r, lastColumn").Select


End Sub