Copy formula into newly inserted columns

nevers0220

New Member
Joined
Nov 28, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have a macro that inserts two new columns into my file and then applies two formula to each of those columns. I am stuck on how to copy those formulas across the whole spreadsheet. Below is the macro and the screenshot at the bottom is the file I'm working on. Columns K & L are the newly inserted columns and I'm looking to copy the formulas in K & L to O & P and so on.

Sub Format()
Dim iX As Integer
Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
If ws.Name <> "One Stream Pivot" And ws.Name <> "Property TB One Stream" And ws.Name <> "Mapping" And ws.Name <> "OakTree One Stream" And ws.Name <> "OakTree Mapping" And ws.Name <> "FS Mapping" And ws.Name <> "Entity Table" Then
ws.Activate
For iX = 11 To 40 Step 3
Columns(iX).Insert Shift:=x1ToRight
Next iX
For iX = 12 To 40 Step 4
Columns(iX).Insert Shift:=x1ToRight
Next iX

Range("K13").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC1,'One Stream Pivot'!C1:C34,MATCH(R7C[-2],'One Stream Pivot'!R1,0),0),0)"
Range("K13").Select
Selection.AutoFill Destination:=Range("K13:K" & Range("B" & Rows.Count).End(xlUp).Row)
Range("K13:K195").Select
Range("L13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("L13").Select
Selection.AutoFill Destination:=Range("L13:L" & Range("B" & Rows.Count).End(xlUp).Row)


End If
Next ws



End Sub




1669652524523.png


Thanks!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,214,985
Messages
6,122,605
Members
449,089
Latest member
Motoracer88

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
Back
Top