Paste Absolute value with VBA

[GPUSA810]

I am new to VBA, but I have been able to edit my recording enough to get what I want to this point. Here is what I have so far:

 ActiveCell.Offset(-12, 0).Select    Range(ActiveCell, ActiveCell.Offset(0, 16)).Select
    Selection.Copy
    ActiveCell.Offset(12, 0).Select
    ActiveSheet.Paste
    ActiveCell.Select
    Application.CutCopyMode = False
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/[COLOR=#ff0000]R[8]C[3][/COLOR]*RC[-1])"
    ActiveCell.Select
    Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(7, 0)), Type:=xlFillDefault
    Range(ActiveCell, ActiveCell.Offset(7, 0)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

The above red relative cell entry results in the correct cell for the edited formula, but for the AutoFill to work properly in the remaining code I need the resulting cell reference to be absolute:

Line: ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/R[8]C[3]*RC[-1])"
Result: "K580" = IF(H580=0,"-",(E580/N576)*J580)
Wanted Result: "K580" = IF(H580=0,"-",(E580/$N$576)*J580)

Thanks

 

[GPUSA810]

Sorry I am trying to see if I can get a screen shot to you. For each day the there are eight data entry rows and columns A through K.

Columns A, C, E, G, J, and K are formulas to provide data for the calculations I am trying to AutoFill in K.
Columns B, D, F, H, I are daily entries from the Production Manager.

The eight data entry rows start every twelfth row.

Column K would being in row 568. Each cell down to K575 being the same formula with the ascending cell references and the Absolute cell reference in that formula referring to $N$576.
K576= Auto sum of K568:K575
K577 Blank
K578 Black for border (separation for next day)
K579 Blank

This pattern repeats again:

Column K would being in row 580. Each cell down to K587 being the same formula with the ascending cell references and the Absolute cell reference in that formula referring to $N$588.
K588= Auto sum of K580:K587
K589 Blank
K590 Black for border (separation for next day)
K591 Blank

Does that help??

 

[Joe4]

What would be the very first row you would ever need this to start at?

 

[GPUSA810]

Row 4

 

[GPUSA810]

There is any way to rewrite:

[COLOR=#333333]ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/ ""$""R[8] ""$"" C[3]*RC[-1])"[/COLOR]

To get that cell reference absolute?

 

[Joe4]

Try this:

Sub MyMacro()

    Dim lastRow As Long
    Dim startRow As Long
    Dim firstRow As Long
    Dim startRange As Range
    Dim newRange As Range
    
    Application.ScreenUpdating = False
    
'   Find last popuated row currently in column K
    lastRow = Cells(Rows.Count, "K").End(xlUp).Row
    
'   Calculate starting row in column K
    startRow = Int(lastRow / 12) * 12 + 4
    
'   Set starting range to copy from
    Set startRange = Range(Cells(startRow, "K"), Cells(startRow, "AA"))

'   Copy row 12 rows down
    startRange.Copy startRange.Offset(12, 0)
    
'   Calculate first row in new range
    firstRow = startRow + 12
    
'   Set new range (first 8 rows of column K)
    Set newRange = Range(Cells(firstRow, "K"), Cells(firstRow + 7, "K"))
    
    With newRange
        .FormulaR1C1 = "=IF(RC[-3]=0,""-"",(RC[-6]/R" & firstRow & "C14)*RC[-1])"
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
    End With
    
    With newRange.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    
    newRange.Borders(xlEdgeRight).LineStyle = xlNone
    newRange.Borders(xlInsideVertical).LineStyle = xlNone
    newRange.Borders(xlInsideHorizontal).LineStyle = xlNone

    Application.ScreenUpdating = True

End Sub

I added some documentation to the code so you can see what is going on.

 

[GPUSA810]

Thank you so much for your time. It is running.

It took a minute for it to go. It started 36cells down first run.
I adjusted the formula to get the absolute cell reference I needed.

Can I use this for other columns?

To change sheets I would just Activate the sheet I want it to run in?

Thank you again.

 

[Joe4]

Can I use this for other columns?

Sure.

To change sheets I would just Activate the sheet I want it to run in?

That is one way. If it is always going to run against specific sheets, you can add that sheet reference to the code.

 

[GPUSA810]

Thank you!
I will work with it a see what i can make it do.