Alter VBA code to get Category & Criteria from different sheet

[tlc53]

Hi there,

I have the following code which works perfectly in my original spreadsheet but now I want to adapt it to work in my new spreadsheet.

I had help writing this code so I'm a little unsure how to change the coding.

It's the last bit of the code I want to change - .FormulaR1C1 = "=IF(RC[8]=""Grand Total"",RC[8],INDEX(Category,MATCH(LEFT(RC[8],LEN(RC[8])-6)+0,Criteria,0),1))"

The Category and Criteria are now found on sheet "SkyCity Invoice", not the active sheet and it is also capturing more cells.

Was;
Category was: A21:A35
Criteria was: K21:K35

Should now be;
Category: ='SkyCity Invoice'!A21:A120
Criteria: ='SkyCity Invoice'!K21:K120

I tried changing/adding the Named Ranges but that hasn't worked.

Can someone point me in the right direction please?

Thanks!!

Sub ClientNarrative()

    Range("A3").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Sheets("Invoice Data").Columns("A:K").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("K20:K120"), CopyToRange:=Range("A3:K3"), Unique:= _
        False
            Range("A1").Select

If Range("A4") = 0 Then Exit Sub

Application.ScreenUpdating = False
Dim r As Range
Dim cust As Range

Set r = Range("A3:K" & Range("A" & Rows.Count).End(xlUp).Row)
Set cust = Sheets("SkyCity Invoice").Range("K20:K120")


cust.Offset(, 1).Value = Application.Transpose(Array(1, 2, 3, 4, 5, 6))
r.Columns(11).Offset(1, 1).Resize(r.Rows.Count - 1).FormulaR1C1 = "=VLOOKUP(RC[-1],R20C11:R25C12,2,0)"
r.Value = r.Value
Set r = r.Resize(r.Rows.Count, r.Columns.Count + 1)
r.Sort Key1:=[L52], Order1:=xlAscending, Header:=xlYes
r.Columns(12).ClearContents
cust.Offset(, 1).Value = vbNullString
Application.ScreenUpdating = True


Range("A4").Select


    Selection.CurrentRegion.Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
        End With
        
        Dim sSortOrder As String

sSortOrder = Join(Filter(Application.Transpose(Sheets("SkyCity Invoice").Range("K20:K120").Value), "Blank", False), ",")
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add2 Key:=Selection.Columns(Selection.Columns.Count), Order:=xlAscending, CustomOrder:="""" & sSortOrder & """"
With ActiveSheet.Sort
  .SetRange Selection
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
    Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(6, 7, 9) _
        , Replace:=False, PageBreaks:=False, SummaryBelowData:=True
 
 
  Application.ScreenUpdating = False
  ActiveSheet.Outline.ShowLevels RowLevels:=2
  With Range("K" & Rows.Count).End(xlUp).CurrentRegion
    With .Offset(1).Resize(.Rows.Count - 1, 10).SpecialCells(xlVisible).Rows
      .Font.Bold = True
      .Interior.Color = 14277081
      .BorderAround xlContinuous
      .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    End With
    With Intersect(.Columns(3), .SpecialCells(xlVisible), .SpecialCells(xlBlanks))
      .FormulaR1C1 = "=IF(RC[8]=""Grand Total"",RC[8],INDEX(Category,MATCH(LEFT(RC[8],LEN(RC[8])-6)+0,Criteria,0),1))"
    End With
  End With
  ActiveSheet.Outline.ShowLevels RowLevels:=3
  Application.ScreenUpdating = True


            Range("A1").Select
    End Sub

 

[tlc53]

I solved this by renaming the ranges Catergory1 and Criteria1