Run-time error '1004' with Named cells

mahaneca · January 28th, 2009, 04:23 PM

Hi - I'm new to VBA programming and am trying to write a macro in Excel. I have a named cell (LR_FileName) where the user will input the name of the file. The macro will then activate that file (should already be open) and select and copy cells from that file to the file with the macro. However, when I run the code, I receive the following error:
Run-time error '1004':
Method 'Range' of object' _Global' failed

Here is the section of code that it is tripping up on:

Code:

Sub CDSS_Data_Fill_2_ColB()
    Windows(Range("CDSS_Name").Value).Activate
    Application.ScreenUpdating = False
    Range("G2").Select
    Selection.Copy
    Windows(Range("LR_FileName").Value).Activate
    Range("B6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

When I run the debug, the line Windows(Range("LR_FileName").Value).Activate is highlighted.

If I change the code to just activate a file name instead of a referenced Named cell, I get the same error but on the next line (Range("B6").Select

Thanks for any help!

Shasur · January 29th, 2009, 01:04 AM

I think you need to check for two things here

1. Presence of Named Range
2. Presence of the Workbook in the current Excel instance.

Here is a hint which you can use

Dim RngCDSS As Range
Dim oWB As Workbook

Set RngCDSS = Range("CDSS_Name")
If RngCDSS Is Nothing Then MsgBox "Name CDSS_Name Doesn't Exist": Exit Sub

For Each oWB In Workbooks
If InStr(1, oWB.name, RngCDSS.Value) <> 0 Then
Windows(oWB.name).Activate
Exit For
End If
Next

Set RngLR = Range("LR_FileName")
If RngLR Is Nothing Then MsgBox "Name LR_FileName Doesn't Exist": Exit Sub

For Each oWB In Workbooks
If InStr(1, oWB.name, RngLR .Value) <> 0 Then
Windows(oWB.name).Activate
Exit For
End If
Next

Cheers
Shasur