Hi,

I have a macro that "refreshes" a sheet by copy-pasting the functions in a given cell downwards (all of the cells that are copied to be pasted are in row 4). The macro works, except not for spinners. I have five spinners (in cells O4, R4, U4, X4, and AA4) that need to be copied downwards x number of times (depending on the number of user-entered values in column B), all with links to cells AE4, AF4, AG4, AH4, and AI4, respectively. The spinners that would be pasted downwards would need to reference cells in the same row (i.e. the spinner that was pasted in O5 should reference AE5, the spinner pasted in O6 should reference AE6, etc.). I've attached the macro for reference.

I'm new to VBA so am figuring things out as I go, but am stumped on this one. Any ideas? Thanks!

Option Explicit

Sub Refresh()

    Dim xSheet As Worksheet
    Dim RefreshRange As Range
    Dim LastCell As Range
    
    Set xSheet = ActiveSheet
    Set RefreshRange = xSheet.Range("B3")
    Set LastCell = xSheet.Range("B65000").End(xlUp)
    
    'Clear contents
    xSheet.Select
    xSheet.Range("A5:A" & LastCell.Row).ClearContents
    xSheet.Range("C5:I" & LastCell.Row).ClearContents
    xSheet.Range("L5:M" & LastCell.Row).ClearContents
    xSheet.Range("O5:P" & LastCell.Row).ClearContents
    xSheet.Range("R5:S" & LastCell.Row).ClearContents
    xSheet.Range("U5:V" & LastCell.Row).ClearContents
    xSheet.Range("X5:Y" & LastCell.Row).ClearContents
    xSheet.Range("AA5:FG" & LastCell.Row).ClearContents
    xSheet.Range("AE5:AI" & LastCell.Row).ClearContents
    xSheet.Range("AL4:AP4").ClearContents
    xSheet.Range("AR4:AV4").ClearContents
    xSheet.Range("AX4:BG4").ClearContents
    xSheet.Range("BI4:BR4").ClearContents
    xSheet.Range("BT4:CC4").ClearContents
    xSheet.Range("CE4:CN4").ClearContents
    xSheet.Range("CP4:CY4").ClearContents
    xSheet.Range("DA4:DE4").ClearContents
    xSheet.Range("DG4:DK4").ClearContents
    xSheet.Range("DM4:DQ4").ClearContents
    xSheet.Range("DS4:DW4").ClearContents
    xSheet.Range("DY4:EC4").ClearContents
    xSheet.Range("EE4:EI4").ClearContents
    xSheet.Range("EK4:EO4").ClearContents
    xSheet.Range("EQ4:EU4").ClearContents
    xSheet.Range("EW4:FA4").ClearContents
    xSheet.Range("FC4:FG4").ClearContents
    
    'Refresh data
    RefreshRange.Select
    'Application.CommandBars.FindControl(Tag:="menurefreshdatacell").Execute
    
    'if there are no values retrieved then exit the macro
    If Not LastCell.Row > RefreshRange.Row Then Exit Sub
    
    'copy & paste formulas
        
        xSheet.Range("A4").Copy
        xSheet.Range("A5:A" & LastCell.Row).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        xSheet.Range("C4:I4").Copy
        xSheet.Range("C5:I" & LastCell.Row).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        xSheet.Range("L4:M4").Copy
        xSheet.Range("L5:M" & LastCell.Row).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        xSheet.Range("O4:P4").Copy
        xSheet.Range("O5:P" & LastCell.Row).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        xSheet.Range("R4:S4").Copy
        xSheet.Range("R5:S" & LastCell.Row).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        xSheet.Range("U4:V4").Copy
        xSheet.Range("U5:V" & LastCell.Row).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        xSheet.Range("X4:Y4").Copy
        xSheet.Range("X5:Y" & LastCell.Row).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        xSheet.Range("AA4:AD4").Copy
        xSheet.Range("AA5:AD" & LastCell.Row).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        xSheet.Range("AE4:AI4").Copy
        xSheet.Range("AE5:AI" & LastCell.Row).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        xSheet.Range("AK4:FG4").Copy
        xSheet.Range("AK5:FG" & LastCell.Row).PasteSpecial xlPasteAll
        Application.CutCopyMode = False
        
        xSheet.Range("AK4:FG4").Select
        Application.CommandBars.FindControl(Tag:="menurefreshdatacell").Execute
            
    xSheet.Range("A1").Select
    
End Sub