Macro Help - Pasting

[kevin123]

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!

[PAB]

Hi, welcome to the board kevin123 .

Give this a go for the first part...

Dim i
    For i = 9 To 39
        If Range("B" & CStr(i)).Value = "" Then
            Range("B" & i).Value = ActiveSheet.Value
        Exit For
    End If
Next i

I hope this helps!

[kevin123]

Hi PAB, thanks for your reply. When I try running this I get an a Runtime error 438: Object doesnt support this property or method. When I choose debug the following line is highlighted: "Range("B" & i).Value = ActiveSheet.Value"

Also a note, the information I'm copying is from another excel instance, could this be part of a problem? I remember awhile back using different windowed excels gave me some issues with pasting.

[PAB]

That's strange, that eror normally occurs when there is a form involved!

[kevin123]

Hmm, I'm not well versed in VBA but I notice there is no paste command? It is just telling it to equal the value?

Also more info to maybe shed light, I am copying three columns of data. So I paste in cell B,i and data pastes to B,i, C,i and D,i. Could this be a cause of a problem?