insert formuls into visible cells only

[jordanburch]

Sub CRISFILTERFINAL3()
'
' CRISFILTERFINAL3 Macro
'

' Sheets.Add.Name = "CRIS"
Sheets.Add.Name = "CRIS"
Dim Xrow As Long, WS As Worksheet, ws2 As Worksheet, dng As Range, dng2 As Range, dng3 As Range, dng4 As Range, dng5 As Range, dng6 As Range, dng7 As Range, dng8 As Range, dng9 As Range, dng10 As Range, dng11 As Range, dng12 As Range, dng13 As Range, dng14 As Range, dng15 As Range, dng16 As Range, dng17 As Range, dng18 As Range, dng19 As Range, dng20 As Range, dng21 As Range, dng22 As Range

Xrow = Cells(Rows.Count, "K").End(xlUp).Row


Set WS = ThisWorkbook.Worksheets("CRIS")
Set ws2 = ThisWorkbook.Worksheets("MAY FY20 IDARRS")
Set dng = WS.Range("af2:af" & Xrow)
Set dng2 = WS.Range("ag2:ag" & Xrow)
Set dng3 = WS.Range("ah2:ah" & Xrow)
Set dng4 = WS.Range("ai2:ai" & Xrow)
Set dng5 = WS.Range("aj2:aj" & Xrow)
Set dng6 = WS.Range("ak2:ak" & Xrow)
Set dng7 = WS.Range("al2:al" & Xrow)
Set dng8 = ws2.Range("ao2:ao" & Xrow)
Set dng9 = ws2.Range("ap2:ap" & Xrow)
Set dng10 = ws2.Range("aq1:aq" & Xrow)
Set dng11 = ws2.Range("ar1:ar" & Xrow)
Set dng12 = ws2.Range("as2:as" & Xrow)
Set dng13 = ws2.Range("at2:at" & Xrow)
Set dng14 = ws2.Range("au2:au" & Xrow)
Set dng15 = ws2.Range("av2:av" & Xrow)
Set dng16 = ws2.Range("aw2:aw" & Xrow)
Set dng17 = ws2.Range("A2:A" & Xrow)
Set dng18 = ws2.Range("B2:B" & Xrow)
Set dng19 = ws2.Range("C2:C" & Xrow)
Set dng20 = ws2.Range("d2:D" & Xrow)
Set dng21 = ws2.Range("E2:E" & Xrow)
Set dng22 = ws2.Range("F2:F" & Xrow)




Sheets("3875 Indy").Select
Cells.Select
Selection.Copy
Sheets("CRIS").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:F").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Recon Period"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Account"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Source2"
Range("D1").Select
ActiveCell.FormulaR1C1 = "ALC"
Range("E1").Select
ActiveCell.FormulaR1C1 = "FDRI"
Range("F1").Select
ActiveCell.FormulaR1C1 = "DPT"
Cells.Select
Cells.EntireColumn.AutoFit

Range("AF1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AH1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AI1").Select
ActiveCell.FormulaR1C1 = "subkey"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = ""
Range("AI1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AJ1").Select
ActiveCell.FormulaR1C1 = "Master"
Range("AK1").Select
ActiveCell.FormulaR1C1 = "match"
'Set dng = WS.Range("af2:af" & Xrow)Set dng2 = WS.Range("ag2:ag" & Xrow) Set dng3 = WS.Range("ah2:ah" & Xrow) Set dng4 = WS.Range("ai2:ai" & Xrow)
'Set dng5 = WS.Range("aj2:aj" & Xrow)Set dng6 = WS.Range("ak2:ak" & Xrow) Set dng7 = WS.Range("al2:al" & Xrow)Set dng8 = ws2.Range("ao2:ao" & Xrow)
'Set dng9 = ws2.Range("ap2:ap" & Xrow)Set dng10 = ws2.Range("aq1:aq" & Xrow)Set dng11 = ws2.Range("ar1:ar" & Xrow)Set dng12 = ws2.Range("as2:as" & Xrow)
'Set dng13 = ws2.Range("at2:at" & Xrow)Set dng14 = ws2.Range("au2:au" & Xrow)Set dng15 = ws2.Range("av2:av" & Xrow)Set dng16 = ws2.Range("aw2:aw" & Xrow)
'Set dng17 = ws2.Range("A2:A" & Xrow)Set dng18 = ws2.Range("B2:B" & Xrow)Set dng19 = ws2.Range("C2:C" & Xrow)Set dng20 = ws2.Range("d2:D" & Xrow)
'Set dng21 = ws2.Range("E2:E" & Xrow)Set dng22 = ws2.Range("F2:F" & Xrow)
dng.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+IF(RC[-20]="""",""0000"","""")"
dng2.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+CONCATENATE(RC[-22],RC[-1],RC[-21])"
dng3.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+SUMIF(C[-1],RC[-1],C[-8])"
dng5.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+CONCATENATE(RC[-3],RC[-2])"
Sheets("MAY FY20 IDARRS").Select
Cells.Select
Selection.AutoFilter
Selection.AutoFilter

ActiveSheet.Range("$A$1:$AW$6000").AutoFilter Field:=39, Criteria1:= _
"=Not on CO SAR", Operator:=xlOr, Criteria2:="="

ActiveSheet.Range("$A$1:$AW$6000").AutoFilter Field:=34, Criteria1:= _
"2 - ALC 5700"

ActiveSheet.Range("$A$1:$AW$6000").AutoFilter Field:=36

ActiveSheet.Range("$A$1:$AW$6000").AutoFilter Field:=43, Criteria1:= _
"3875.001"
Range("AS1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AT1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AU1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AV1").Select
ActiveCell.FormulaR1C1 = "sub key"
Range("AW1").Select
ActiveCell.FormulaR1C1 = "master key"
'Set dng5 = WS.Range("aj2:aj" & Xrow)Set dng6 = WS.Range("ak2:ak" & Xrow) Set dng7 = WS.Range("al2:al" & Xrow)Set dng8 = ws2.Range("ao2:ao" & Xrow)
'Set dng9 = ws2.Range("ap2:ap" & Xrow)Set dng10 = ws2.Range("aq1:aq" & Xrow)Set dng11 = ws2.Range("ar1:ar" & Xrow)Set dng12 = ws2.Range("as2:as" & Xrow)
'Set dng13 = ws2.Range("at2:at" & Xrow)Set dng14 = ws2.Range("au2:au" & Xrow)Set dng15 = ws2.Range("av2:av" & Xrow)Set dng16 = ws2.Range("aw2:aw" & Xrow)
'Set dng17 = ws2.Range("A2:A" & Xrow)Set dng18 = ws2.Range("B2:B" & Xrow)Set dng19 = ws2.Range("C2:C" & Xrow)Set dng20 = ws2.Range("d2:D" & Xrow)
'Set dng21 = ws2.Range("E2:E" & Xrow)Set dng22 = ws2.Range("F2:F" & Xrow)
dng12.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+CONCATENATE(RC[-40],RC[-39])"
dng13.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+SUMIF(C[-1],RC[-1],C[-9])"
dng13.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+CONCATENATE(RC[-4],RC[-3])"
Sheets("CRIS").Select


dng6.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+MATCH(RC[-1],'MAY FY20 IDARRS'!C[12],0)"

dng17.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+DATE(YEAR(TODAY()),MONTH(TODAY()),1)"


dng18.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+INDEX('MAY FY20 IDARRS'!C[-1]:C[47],CRIS!RC[35],43)"

dng19.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "CRIS"

dng20.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "2 - ALC 5700"

dng21.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+INDEX('MAY FY20 IDARRS'!C[-4]:C[44],CRIS!RC[32],22)"

dng22.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+INDEX('MAY FY20 IDARRS'!C[-5]:C[43],CRIS!RC[31],1)"

Cells.Select
Range("AC1").Activate
Selection.AutoFilter
Sheets("MAY FY20 IDARRS").Select

dng5.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "COMPLETE"

dng7.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "CRIS"

End Sub


hey guys i have the above code. I will fix where the formulas and what ranges go i got a little ocnfused with 22 of them. My problem is that it is only inserting it into the top row. I want it to insert into every row that is visble beneath the headers row. Any ideas why this isnt working?

 

[onlyadrafter]

Hello,

I think it is to do with

Xrow = Cells(Rows.Count, "K").End(xlUp).Row

being just after add sheet CRIS. When you add a new sheet it is blank, so no cells have data.

As a test, change the above code to

Xrow = 5

and see if this makes a difference. If so, then you need to get the last row somewhere else.