Product upsell/crosssell/linking excel macro vba

upsells.vba

Sub GenerateUpsellCSV()
'
' Macro1 Macro
'

' Find out how many rows are used and save as variable

    lastrow = ActiveSheet.UsedRange.Rows.Count
    
' Insert formulas across the first row of data

    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-2]=RC[-2],RC[-1]&""|""&R[-1]C,RC[-1])"
    
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(C[-3]:C[-1],MATCH(RC[-3],C[-3],0)+(COUNTIF(C[-3],RC[-3])-1),3)"
    
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],RC[-3],"""")"
    
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(LEFT(RC[-1],1)=""|"",RIGHT(RC[-1],LEN(RC[-1])-1),RC[-1])"
    
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RIGHT(RC[-1],1)=""|"",LEFT(RC[-1],LEN(RC[-1])-1),RC[-1])"
    
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],""||"",""|"")"
    
' Copy formulas down each column of data

    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & lastrow)

    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D" & lastrow)

    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E" & lastrow)

    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F" & lastrow)

    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G" & lastrow)

    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H" & lastrow)

' Copy entire upsells column and paste into new sheet

    Columns("H:H").Select
    Selection.Copy
    Sheets("upsells").Select
    Columns("B:B").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

' Copy entire sku column and paste into new sheet

    Sheets("base_sku").Select
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("upsells").Select
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
' Label column headers in new sheet

    Range("B1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "upsell_skus"
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "sku"
    
' Save sheet as new csv file
    
    Worksheets("upsells").Copy
    With ActiveWorkbook
        .SaveAs Filename:="C:\Users\Benjamin\Downloads\upsells.csv", FileFormat:=xlCSV
        .Close SaveChanges:=False
    End With
    
'  Clear data for a fresh start next time

    Sheets("upsells").UsedRange.ClearContents
    Sheets("base_sku").UsedRange.ClearContents

'  Relabel headers on base_sku sheet

    Sheets("base_sku").Select
    Range("B1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "sku"
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "base"
End Sub