if cell value is 2,3,4,5,..etc then insert row

bhandari

Active Member
Joined
Oct 17, 2017
Messages
359
i need help with macro

if cell value is 2,3,4,5,..etc then insert row
Eg:A3=2

add 2 rows after D column
"copy the D column formulas" and paste it to E AND F to the new added columns
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
It would be helpful if you provided more details:
if cell value is 2,3,4,5,..etc then insert row
Which cell?
Are we just looking at one cell, or multiple cells?
Will it be the same cell every time?
If not, how can we determine which cell we need to look at?

"copy the D column formulas" and paste it to E AND F to the new added columns
For which rows?
Just one row or all rows?

Also, should this VBA code run manually against existing data, or should it run automatically as a value is placed in your designated cell?
 

bhandari

Active Member
Joined
Oct 17, 2017
Messages
359
Re: if cell value is 2,3,4,5,..etc then insert column

Can u please replace with column in the place of row in 1st thread

I can't edit the post iam trying to explain here again

My intention to insert column
Eg if A3=2

So add 2 columns after D
So new columns are E and F need to be created

E and F columns should have D column formulas
--D5:D67 have formulas
I want to paste those formulas in E and F also
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
Re: if cell value is 2,3,4,5,..etc then insert column

Don't worry about editing your original post - you clarified it in your follow-up posts.

Right-click on the sheet tab name at the bottom of your screen, select "View Code", and paste this VBA code in the resulting VB Editor Window:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Run if cell A3 is updated
    If Target.Address(0, 0) = "A3" Then
'       If number entered is greater than zero
        If Target > 0 Then
            Application.EnableEvents = False
'           Insert columns
            Range(Cells(5, 5), Cells(5, Target.Value + 4)).EntireColumn.Insert
'           Copy formulas
            Range("D5:D67").Copy Range(Cells(5, 5), Cells(5, Target.Value + 4))
            Application.EnableEvents = True
        End If
    End If

End Sub
This will automatically insert columns and copy the formulas whenever you enter a value in cell A3.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top