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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,215,514
Messages
6,125,273
Members
449,220
Latest member
Excel Master

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