Run-Time Error '1004'

[omakbigd]

I have a Macro that was created by someone else with no notes. The macro moves information from one spreadsheet (A) to separate tabs on another spreadsheet (B).

I am trying to add a new tab on spreadsheet (B)

I copied one of the other tab macros within the big macro and changed the tab name to the new tab name and it will not work.

The first macro works when you step into it, it will go to the "_om" tab in (B) and paste the information even though it says "_om2". The second macro is the one I created, and when it gets to "_pc" it gives me an error.

Run-TIme error '1004': The text you entered is not valid reference or defined name

Windows("X2173.xlsx").Activate
Range("A2").Select
Cells.Find(What:="Meter ID : X2146", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Activate
ActiveCell.Offset(8, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("_SUBREAD.XLSM").Activate
Application.Goto Reference:="_om2"
ActiveSheet.Paste
Sheets("transf").Select
Range("A1").Select


Windows("X2173.xlsx").Activate
Range("A2").Select
Cells.Find(What:="Meter ID : X2599", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Activate
ActiveCell.Offset(8, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("_SUBREAD.XLSM").Activate
Application.Goto Reference:="_pc"
ActiveSheet.Paste

Is there a way to designate the _pc as a name. I was told to use a Dim ?? As ?? statement. If that is correct how would you state that?

[HenryCrun]

The reference in Application.Goto Reference:="_om2" refers to a named range not a worksheet.

In the original, I'm guessing there is a named range "_om2" on sheet "_om" (which is why it works, despite the reference in the macro not matching the sheet name).

In your macro, it is looking for a range named "_pc", which presumably doesn't exist.

[tlafferty]

You need a line of code like this:

ActiveWorkbook.Names.Add Name:="_PC", RefersToR1C1:="=REPLACEWITHSHEETNAME!REPLACEWITHCELLREFERENCES"

[omakbigd]

So the line of code would state

Activeworkbook.Names.Add Name:= "_pc", RefersToR1C1:="=_pc!F2"

[Mordred]

Hi omakbigd, please use code tags around all code, even if it is just one line.

I added them for you this time but remember to use them in the future.

Thank you.

[omakbigd]

How do I name a range _pc with in a Sheet pc_? I am trying to learn this on the fly.

Thanks

[omakbigd]

I made some changes to the code and am now getting a different error.

The new error is Run-time error '1004' Reference is not valid

The line I added is,

 ActiveWorkbook.Names.Add Name:="pc_", RefersToR1C1:="_pc!R2C11"

The line after this is the bug.

    Windows("X2173.xlsx").Activate
    Range("A2").Select
    Cells.Find(What:="Meter ID :  X2146", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=True).Activate
    ActiveCell.Offset(8, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("_SUBREAD.XLSM").Activate
    Application.Goto Reference:="_om2"
    ActiveSheet.Paste
    Sheets("transf").Select
    Range("A1").Select
    
    
    Windows("X2173.xlsx").Activate
    Range("A2").Select
    Cells.Find(What:="Meter ID :  X2599", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=True).Activate
    ActiveCell.Offset(8, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("_SUBREAD.XLSM").Activate
    ActiveWorkbook.Names.Add Name:="pc_", RefersToR1C1:="_pc!R2C11"
    Application.Goto Reference:="_pc"
    ActiveSheet.Paste

[tlafferty]

I took a look at your workbooks, and the one with the code modules is a bit of a mess. There's a lot that could be done to clean things up, but it would take me a while to wrap my head around what your trying to do. It looks like most of it has to do with copying, pasting and deleting data. What I need to know is which sub routine contains the code you'd like modified. I searched for the added line:

 ActiveWorkbook.Names.Add Name:="pc_", RefersToR1C1:="_pc!R2C11"

and couldn't find it in any of your code modules. In the attached book, I consolidated all of the modules into one module, and I need you to let me know:
1) which routine needs modification
2) which book to modules should be in

[Mordred]

This is my own two cents, well, probably only one cent. Your code needs to be completely redone in my opinion. I've stepped through what you have from the following point

    Range("A2").Select
    Cells.Find(What:="Meter ID :  X2146", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=True).Activate
    ActiveCell.Offset(8, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("_SUBREADa.XLSM").Activate
    Application.Goto Reference:="_om2"
    ActiveSheet.Paste
    Sheets("transf").Select

I didn't get an error but it is impossible to step through all your lines of code because of the multiple workbook selections and range selections / activations. This, coupled with tlafferty's (correct) statement

the code modules is a bit of a mess

makes me think that it may be easier for those that will help if you explain the processes of the workbook. If you step through the requirements with the forum, you should have a complete project in know time. Otherwise, your fix will be part guessing by the forum members such as tlafferty and perhaps too much work (time wise) for the kind volunteers.


Unless, of course, tlafferty solved your problem for which you should just ignore my ramblings .

Good Luck!