nevers0220
New Member
- Joined
- Nov 28, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- 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
Thanks!
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
Thanks!