Macro: transit formula from multiple rows

[Maci3j]

Hi Everyone,

I have a problem and hope you could help me with a macro.

I have a value in cell A2 and I would like to transit the formulas from range B2:AC2 to the next rows if I add values in the next rows of column A.
The range of filled in values in column A will be random.

Thank you in advance!

Kind regards,

 

[Joe4]

Is this what you are looking for?

Sub MyCopyRows()

    Dim lr As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Copy formulas from B2:AC2 down to last row
    If lr > 2 Then Range("B2:AC2").Copy Range("B3:B" & lr)
    
End Sub

 

[Maci3j]

Is this what you are looking for?

Sub MyCopyRows()

    Dim lr As Long
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Copy formulas from B2:AC2 down to last row
    If lr > 2 Then Range("B2:AC2").Copy Range("B3:B" & lr)
   
End Sub

yes!! That works great.
Is there a possibility to change it to a worksheet module instead of the normal module?
I tried to copy it into worksheet module but it doesn't seem to work.

 

[Joe4]

Is there a possibility to change it to a worksheet module instead of the normal module?

So, do you want it to run automatically whenever a change is made to column A?
If so, this modification would work:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
   
'   See if one single cell update in column A
    If Target.CountLarge = 1 And Target.Column = 1 Then
        Application.EnableEvents = False
'       Find last row in column A with data
        lr = Cells(Rows.Count, "A").End(xlUp).Row
'       Copy formulas from B2:AC2 down to last row
        If lr > 2 Then Range("B2:AC2").Copy Range("B3:B" & lr)
        Application.EnableEvents = True
    End If
   
End Sub

 

[Maci3j]

or the alternative is: how can I combine both macro's into one module?

Macro1:

Sub OTIF()
'
' OTIF Macro
'

'
Sheets("OTIF").Select
Range("C1,I1,K1,M1").Select
Range("M1").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("E:N").Select
Selection.NumberFormat = "m/d/yyyy"
Range("F12").Select
Selection.AutoFilter
Columns("A:O").Select
Columns("A:O").EntireColumn.AutoFit
Range("A1").Select
Sheets("Instructie").Select
End Sub


Macro 2

So, do you want it to run automatically whenever a change is made to column A?
If so, this modification would work:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lr As Long
  
'   See if one single cell update in column A
    If Target.CountLarge = 1 And Target.Column = 1 Then
        Application.EnableEvents = False
'       Find last row in column A with data
        lr = Cells(Rows.Count, "A").End(xlUp).Row
'       Copy formulas from B2:AC2 down to last row
        If lr > 2 Then Range("B2:AC2").Copy Range("B3:B" & lr)
        Application.EnableEvents = True
    End If
  
End Sub

It works great.
Many thanks!

 

[Joe4]

or the alternative is: how can I combine both macro's into one module?

Just take the "guts" of your OTIF code block, and paste it into the other one, below the current code.

 

[Maci3j]

thanks.
I created a macro module with multiple macro's in it:

Sub RunAllMacros()

OTIF
DATA_CopyColumnFromOTIF


End Sub

 

[Joe4]

thanks.
I created a macro module with multiple macro's in it:

Sub RunAllMacros()

OTIF
DATA_CopyColumnFromOTIF


End Sub

Yep, that is another option.