Adjust Cell References for Formulas

Sherifa

New Member
Joined
Oct 23, 2017
Messages
45
I've copied a row from a spreadsheet, I then get the code to find the last row in each sheet and paste the summary row, which includes sum and count formulas, but the row number changes for each sheet.

How do I get the pasted formula to adjust to the number of rows in the sheet.

Code:
Sub SummaryRow()Dim Wkb As Excel.Workbook
Dim ws As Worksheets
Dim ws_count As Integer
Dim i As Integer
Dim LastRow As Long




Dim TargetRow As Range






    Sheet4.Range("a183:M183").Copy
    
        Set Wkb = Workbooks("VBA1.xlsm")
        ws_count = Wkb.Worksheets.Count
                
                     
        'Begin loop
        For i = 4 To ws_count
              
       
       Set TargetRow = Wkb.Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        
         'Paste Formulas
        TargetRow.PasteSpecial xlPasteFormulas
        TargetRow.PasteSpecial xlPasteFormats
        
        Next i
        


End Sub
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Sherifa

New Member
Joined
Oct 23, 2017
Messages
45
Runtime Error 91

Code:
Sub SummaryRow()Dim Wkb As Excel.Workbook
Dim ws As Worksheets
Dim ws_count As Integer
Dim i As Integer
Dim LastRow As Long




    Sheet4.Range("a183:M183").Copy
    
        Set Wkb = ThisWorkbook
        ws_count = Wkb.Worksheets.Count
                
                     
        'Begin loop
        For i = 4 To ws_count
              
       
       Set TargetRow = Wkb.Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        
        LastRow = Wkb.Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row


         'Paste Formulas
        TargetRow.PasteSpecial xlPasteFormulas
        [B]ws(i).Cells(LastRow + 1, 7).FormulaR1C1 = "=SUM(R4C7:R" & LastRow & "C7)"
        ws(i).Cells(LastRow + 1, 8).FormulaR1C1 = "=COUNT(R4C8:R" & LastRow & "C8)"
        ws(i).Cells(LastRow + 1, 9).FormulaR1C1 = "=SUM(R4C9:R" & LastRow & "C9)"[/B]
        TargetRow.PasteSpecial xlPasteFormats
        
        Next i
        
End Sub

Having problems making the formula dynamic
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,148
Messages
5,527,091
Members
409,745
Latest member
CharlesAlsop

This Week's Hot Topics

Top