Copy Spinners with Macro

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

Any help on this would still be much appreciated...

Thanks for the reply! I've attached a sample book below. Unfortunately, most of the formulas rely on a certain plug-in, which is why I hadn't previously attached the book. That being said, the spinners in question aren't directly related to them, so getting them to copy-paste shouldn't be related to the plug-in's formulas.

The spinners were inserted as form controls, and their properties were set to have a maximum value of 10, moving in increments of 1. The results of clicking them will only be seen on the sheet when the value in column A is set to "Manual" for a given row.

Thanks!

This worked perfectly, thanks a bunch! I appreciate the help.