Run-time error when running macro from personal workbook

Hi,

I have a very simple macro that i use to divide a an entire column (besides the header row) by 1000...

Sub Macro1()

Const x As Long = 1000
Dim cl As Range
Dim rng As Range
 
Set rng = Sheet1.Range("d2", Range("d65536").End(xlUp)).SpecialCells(xlCellTypeConstants)
For Each cl In rng
    cl.Value = cl.Value / x
Next cl
End Sub

It works fine as long as the macro is attached to the workbook in which I am trying to use it. If the macro is attached to the personal workbook, I get a runtime error when I attempt to run it...

Run-time error '1004':

Method 'Range' of object '_Worksheet' failed

When I hit Debug, this line is highlighted...

Set rng = Sheet1.Range("d2", Range("d65536").End(xlUp)).SpecialCells(xlCellTypeConstants)

There appears to be some confusion when the range is defined, but I don't see why which workbook the macro is attached to should make a difference.

I am VERY new to this, so any help would be appreciated.

Thanks,
Corey

Here's another way:

Sub Macro1()
    Dim rBlank  As Range

    Set rBlank = ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks).Cells(1, 1)
    rBlank.Value = 1000
    rBlank.Copy
    Range("D2", Cells(Rows.Count, "D").End(xlUp)).SpecialCells(xlCellTypeConstants, xlNumbers).PasteSpecial _
            Paste:=xlPasteValues, operation:=xlPasteSpecialOperationDivide
    rBlank.ClearContents
End Sub

.

Thanks very much for the replies guys. Rick's code worked like a charm. When I tried shg's code, I got the following error:

Run-time error '1004':

No cells were found

Upon debugging, the following line of code was highlighted...

Set rBlank = ActiveSheet.Cells.SpecialCells(xlCellTypeBlanks).Cells(1, 1)

Like I said, I'm a complete noob, so it's entirely possible that I deployed it wrong. I've just been cutting and pasting these into Visual Basic. Any thoughts?

Thanks again for your replies and patience.

-Corey