asked on

autofill formulas using macro

Hi,

I have data in C to G columns and formulas in A,B columns. And i paste the data in C-G columns everytime below the last pasted data, so when i paste the data i want the formulas to autofill in A&B columns using macro.

Kindly please suggest.

Regards,
Shamili

Shums Faruk

Hi Shamili,

Please post your sample data with formula you are using for A & B Column

Subodh Tiwari (Neeraj)

Assuming your formulas in column A:B start from Row2, place the code given below on the Sheet Module.
To do so, right click the Sheet Tab --> View Code --> Paste the code given below into the opened code window.
So that when you paste your data in column C:G, the formulas in column A:B will be filled down the rows automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
Application.EnableEvents = False
If Not Intersect(Target, Range("C:G")) Is Nothing Then
    lr = ActiveSheet.UsedRange.Rows.Count
    Range("A2:B2").AutoFill Range("A2:B" & lr), xlFillDefault
End If
Application.EnableEvents = True
End Sub

Open in new window

SOLUTION

Simulog

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rdy123 rdy

ASKER

HI Neeraj,

Thank you for your response, can this be added in modules?
i think it cant be added,kindly assist.

SOLUTION

Shums Faruk