Macro Help - Pasting

I am trying to write a macro for updating workbooks for of my accounts. Basically I have a master sheet of some data for all accounts and need to copy and paste each accounts numbers to their own workbook within a certain area. I then need to check a total which is the code at the bottom – so I can view it. Pretty simple stuff just saves me many button clicks.

Anyway here is what I came up with and it works:

Dim i
    For i = 9 To 39
        If Range("B" & CStr(i)).Value = "" Then
            Range("B" & i).Select
            ActiveSheet.Paste
            Exit For
        End If
    Next i
    
Range("AR53").Select
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollColumn = 26

The problem is this is regular paste and I need to paste values (match destination formatting). If I add in the paste values code (PasteSpecial Paste:=xlPasteValues) I get an error: Application-defined or object-defined error.

I’ve tried a couple things but no luck. Can anyone offer insight?

(Also figured to note I will be manually copying the information for the master file, but when I click each accounts file I just want to run a macro to paste and be able to view my total)


I'd appreciate any help!