Copy formula and paste same row to last column used

My data is not set up the same as yours, but in general the issue is you are trying to set a range by mixing address types (R1C1 and "R1").

Using some test data I do not get the correct value for iLastColumn (I think), so here is my formula:

Range(Cells(1, 18), Cells(1, 18 + iLastColumn)).Select

Where: Cells(1, 18) represents your "R1" and
Cells(1, 18 + iLastColumn) represents iLastColumns after "R1"

If you supply a sample of your data I may be able to clean this up, otherwise this should get you going in the right direction.

Hello Chance2,

It looks like it is working the way it is suppose too. However, for some reason it will only go out two columns. What would be the proper reference for iLastColumn?

The series of macro's that this project runs is rather extensive, so I am breaking it up into small pieces, trying to make each of the subtotal routines more dynamic.

Here is the code between defining the variables and the pasting of the formula you showed.

   
Sub Subtotal()

    Dim LastRow As Long
    Dim LastCol As Long
    Dim lastcell As String

 'Define Variables
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.UsedRange.Select
    LastCol = Range("A" & Columns.Count).End(xlToLeft).Column
    lastcell = ActiveCell.SpecialCells(xlLastCell).Address
    iLastColumn = Range("A1").End(xlToRight).Row
    a = Cells(7, Columns.Count).End(xlToLeft).Column

    
 'Add Subtotals
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Subtotal"
    
    
    Range("R1").Select
    Selection.NumberFormat = "#,##0"
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[" & LastRow & "]C)"
    Range("R1").Select
    Selection.Copy
    Range(Cells(1, 18), Cells(1, 18 + iLastColumn)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Does any of this explain why the code would only extend out two columns from Cells(1, 18)?? In this case my last column used should be AL.

Thanks,
Matt

I did not debug all your code (it looks like iLastColumn is using ROW not COLUMN), but I would recommend trying a different iLastColumn formula:

iLastColumn= ActiveCell.SpecialCells(xlLastCell).Column

And then just use Cells(1, iLastColumn) as the second reference in the Range (take out the 18 +).

Hello Chance2,

Something is going weird.

Perhaps I go back to the basics. This was just one of the subtotal calculations I needed to do. So maybe instead of copying them individually, I do all 6 of them at the same time.

Here is the what it looks like:

R1:R6 need to have their formula copied all the way to the last used column which can be found using Row 7.

Maybe this makes it easier to deal with.

Regards,
Matt

Hello Chance2,

I think I figured it out. Here is what I did:

    
    Sub Extend_SubTotals()
    
    Dim LastRow As Long
    Dim LastCol As Long
    Dim iLastColumn As Long
    Dim lastcell As String
    
   
    'Define Variables
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.UsedRange.Select
    LastCol = Range("A" & Columns.Count).End(xlToLeft).Column
    lastcell = ActiveCell.SpecialCells(xlLastCell).Address
    iLastColumn = ActiveCell.SpecialCells(xlLastCell).Column
    a = Cells(7, Columns.Count).End(xlToLeft).Column
    
    Range("R1:R6").Select
    Selection.Copy
    Range(Cells(1, 18), Cells(6, iLastColumn)).Select
    ActiveSheet.Paste
    
'Added some formatting
    Range(Cells(1, 17), Cells(5, iLastColumn)).Select
    Selection.Font.Bold = True
    Selection.Font.Bold = False
    Selection.Font.ColorIndex = 0

    Range(Cells(3, 17), Cells(3, iLastColumn)).Select
    Selection.Font.ColorIndex = 55

Seems to work.

Thanks for your help!! My next question will be about making my pivot table more dynamic...stay tuned:-)

Cheers,
Matt