Why am I getting Error Message 1004?

TotallyConfused · Aug 17, 2017

Hello
Can anyone please tell what is wrong with my code. When I run this Sub CalcWriteMisc() everything works perfectly.

Code:

Sub CalcWriteMisc()               'Calculates and writes misc. items to MiscItemsDB
   Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("DataEntryItemsDB")
   Dim ws3 As Worksheet: Set ws3 = ThisWorkbook.Sheets("Variables")
   Dim ws4 As Worksheet: Set ws4 = ThisWorkbook.Sheets("MiscItemsDB")
   
   Dim SourceRo As Integer        'Row in DataEntryItemsDB
   Dim TotalParts As Integer       'Total of all columns for parts,oil,tires,battery
   Dim TotalLabor As Integer      'Total of all labor columns
   Dim X As Integer                   'Loop counter
X = 1
With ws3                                  'ws3 = ThisWorkbook.Sheets("Variables")
                                               'Calc. source row in DataEntryItemsDB sheet.
   SourceRo = .Range("EXCEL_DATE_V") - 42733    
End With                                   'Jan. 1, 2017 = 42736 so will be first row (row [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] )
With ws2                                   'ws2 = ThisWorkbook.Sheets("DataEntryItemsDB")
   For X = 25 To 34                     'Total all 10 columns of total labor
      TotalLabor = TotalLabor + .Cells(SourceRo, X).Value
   Next X
   For X = 5 To 74                                             
       TotalParts = TotalParts + .Cells(SourceRo,X).Value
    Next X                                                  
TotalParts = TotalParts - TotalLabor
End With                       'End With  ws2 = ThisWorkbook.Sheets("DataEntryItemsDB")
With ws4                       'ws4 = ThisWorkbook.Sheets("MiscItemsDB")
   .Cells(SourceRo, "A").Value = Range("EXCEL_DATE_V")  'Write EXCEL_DATE_V 
   .Cells(SourceRo, "B").Value = TotalParts        'Write TotalParts to current dates row 
   .Cells(SourceRo, "C").Value = TotalLabor       'Write TotalLabor to current dates row 
End With                               'END WITH ws4 = ThisWorkbook.Sheets("MiscItemsDB")
End Sub

Now that I have written the data into cells, I need to create a range name for them. Each day a new row of data will be added, so I am unable to manually create the range names. Immediately after running the sub listed above, I run Sub CreateRangeNames(). This is where my problem starts. On the command of '.Cells(2,1).Select' which is part of 'With ws2' I receive this error message. 'Runtime error 1004 Select method of range class failed'.

Code:

Public Sub CreateRangeNames()      'Create and names a range ALSO places name in NAME MANAGER
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("DataEntryItemsDB")
    Dim ws4 As Worksheet: Set ws4 = ThisWorkbook.Sheets("MiscItemsDB")
    Dim DataEntryItemsDBRn As Range   'Name of range in DataEntryItemsDBRn
    Dim MiscItemsDBRn As Range           'Name of range in MiscItemsDBRn
With ws2                            'ws2 = ThisWorkbook.Sheets("DataEntryItemsDB")
   .Cells(2, 1).Select            'Move cursor to cell A2 and makes it the ActiveCell
        'The following line of code creates the range and names it 'DataEntryItemsDBRn'
   Set DataEntryItemsDBRn = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, _ ActiveCell.End(xlToRight).Column))
   ActiveWorkbook.Names.Add Name:="DataEntryItemsDBRn",_ RefersTo:="=" &  DataEntryItemsDBRn.Address
End With                       'END WITH ws2 = ThisWorkbook.Sheets("DataEntryItemsDB")
With ws4                       'ws4 = ThisWorkbook.Sheets("MiscItemsDB")
   .Cells(2, 1).Select       'Move cursor to cell A2 and makes it the ActiveCell
    Set MiscItemsDBRn = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
    ActiveWorkbook.Names.Add Name:="MiscItemsDBRn", RefersTo:="=" &_ MiscItemsDB.Address         'Enter range name in NAME MANAGER
End With                           'ws4 = ThisWorkbook.Sheets("MiscItemsDB")
End Sub

Notice in both subs, I refer to the same sheets in the DIM and SET statements so nothing different. I discovered that if I have the 'ws2' sheet displayed, when I run this last macro, that part of the macro works okay, HOWEVER, the same error message will now appear on the '.Cells2,1).Select' under 'With ws4'. What's confusing is that when I ran the first Sub, I didn't have ANY of those three sheets on the screen, yet it worked okay. Where am I going wrong?

I don't know if this matters, but I'm writing this on a computer with Vista and with Excel 2010. It will be used on Windows 10 with Excel 2007. THANK YOU for any help.

TotallyConfused (Boy am I ever)

RoryA · Aug 17, 2017

You can't select a cell unless its worksheet is active.

TotallyConfused · Aug 17, 2017

THANK YOU RoryA for your reply. When I first wrote this, I had assumed the purpose of using ws2 & ws4 was to make the sheet assigned to those variables active, but I guess it doesn’t. Thinking about it now, I see how that only assigns something to a variable.

If I may impose on you further, I have a couple of questions:

1) If I inserted the following line just above where I have ‘With ws2’, would that allow me to keep the remainder of the code for that section the same? If this can’t be done, is there any way my code could be adapted to allow me to do this? I like the idea of using ws2 etc. because it seems like a very compact way to code. It saves a lot of typing too.
What I’m talking about would make my code appear like this:

ActiveWorkbook.Worksheets("DataEntryItemsDB").Activate
With ws2
.Cells(2,1).Select
‘remainder of the code placed here
End With

2) Is it possible to have more than one sheet in the same workbook activated at the same time, or does activating one, deactivate all the others? For example, if I typed:

ActiveWorkbook.Worksheets("DataEntryItemsDB").Activate
ActiveWorkbook.Worksheets("Sheet1").Activate

Assuming I had previously used the SET command to assign these two sheets to ws1 and ws2, could I now use the following code? I tried this once, but I don’t think I had the ActiveWorkbook…… code right.

With ws1
‘ whatever code inserted here
End With
With ws2
‘ no do this code inserted here
End With

Once again, a big THANK YOU for your help.

MARK858 · Aug 17, 2017

Just don't select it at all... untested....

Code:

    With ws2                        'ws2 = ThisWorkbook.Sheets("DataEntryItemsDB")
                 'The following line of code creates the range and names it 'DataEntryItemsDBRn'
        Set DataEntryItemsDBRn = .Range(.Cells(2, 1), .Cells(.Cells(2, 1).End(xlDown).Row, _
                                                           .Cells(2, 1).End(xlToRight).Column))
        ActiveWorkbook.Names.Add Name:="DataEntryItemsDBRn", _
                                 RefersTo:="=" & DataEntryItemsDBRn.Address
    End With                   'END WITH ws2 = ThisWorkbook.Sheets("DataEntryItemsDB")
    With ws4                   'ws4 = ThisWorkbook.Sheets("MiscItemsDB")
        
        Set MiscItemsDBRn = .Range(.Cells(2, 1), .Cells(.Cells(2, 1).End(xlDown).Row, .Cells(2, 1).End(xlToRight).Column))
        ActiveWorkbook.Names.Add Name:="MiscItemsDBRn", RefersTo:="=" & MiscItemsDB.Address     'Enter range name in NAME MANAGER
    End With                       'ws4 = ThisWorkbook.Sheets("MiscItemsDB")

TotallyConfused · Aug 20, 2017

Hello Mark

THANK YOU for your reply. Sorry it’s taken me so long to get back here, but family problems have kept me away. I tried your suggestion of not selecting the cell; however, I found that didn’t work. Excel then tried to create the range from wherever my cursor happened to be located, even when surrounded by blank cells. It seems that selecting a cell, places the cursor there and that is what Excel uses as a starting point to create the range.

Again, thanks for your time and reply.

MARK858 · Aug 20, 2017

I tried your suggestion of not selecting the cell; however, I found that didn’t work.

Strange as code below which is the code I posted just using the activesheet rather than your sheets name

Code:

Sub SDF()
With ActiveSheet
                 'The following line of code creates the range and names it 'DataEntryItemsDBRn'
        Set DataEntryItemsDBRn = .Range(.Cells(2, 1), .Cells(.Cells(2, 1).End(xlDown).Row, _
                                                           .Cells(2, 1).End(xlToRight).Column))

MsgBox DataEntryItemsDBRn.Address
End With
End Sub

gives me exactly the same results for the range that the code below does which is your code from post number 1, just using the activesheet rather than your sheets name. So very strange if your original code worked when your Sheets("DataEntryItemsDB") was active.

Code:

Sub FFGG()
With ActiveSheet
.Cells(2, 1).Select
   Set DataEntryItemsDBRn = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, _
   ActiveCell.End(xlToRight).Column))
MsgBox DataEntryItemsDBRn.Address
End With
End Sub

Another option you have is

Code:

Set DataEntryItemsDBRn = .Cells(2, 1).CurrentRegion

MsgBox DataEntryItemsDBRn.Address

RoryA · Aug 21, 2017

The range Address does not include the sheet name by default, which is probably why you have the problem. I'd suggest you use an alternative means of naming the range:

Code:

With ws2                        'ws2 = ThisWorkbook.Sheets("DataEntryItemsDB")
                 'The following line of code creates the range and names it 'DataEntryItemsDBRn'
        Set DataEntryItemsDBRn = .Range(.Cells(2, 1), .Cells(.Cells(2, 1).End(xlDown).Row, _
                                                           .Cells(2, 1).End(xlToRight).Column))
        DataEntryItemsDBRn.Name = "DataEntryItemsDBRn"

    End With                   'END WITH ws2 = ThisWorkbook.Sheets("DataEntryItemsDB")
    With ws4                   'ws4 = ThisWorkbook.Sheets("MiscItemsDB")
        
        Set MiscItemsDBRn = .Range(.Cells(2, 1), .Cells(.Cells(2, 1).End(xlDown).Row, .Cells(2, 1).End(xlToRight).Column))
        MiscItemsDBRn.Name ="MiscItemsDBRn"
    End With                       'ws4 = ThisWorkbook.Sheets("MiscItemsDB")

TotallyConfused · Aug 25, 2017

THANK YOU RoryA for your continued attention to my problem. I sure do appreciate the great help you have given me in explaining where my mistakes were. It seems the more of Excel and VBA that I learn, the less I know. Once again, thanks a lot.

TotallyConfused · Aug 25, 2017

Hello Mark

You have done so much extra work in solving my problem that I can’t begin to THANK you enough. When I started this thread, I had been fighting with this problem for several days. During that time, I think I tried almost every way I could think of. Sitting here now, there is no way I can think back and know exactly how I had everything set up during each one of my tries. When I said, ‘What's confusing is that when I ran the first Sub, I didn't have ANY of those three sheets on the screen, yet it worked okay.’ it is very possible that I DID have one of them active and didn’t notice it at the time. I probably tried some combinations more than once as I’d change something and then try running it again. When things didn’t work, I’d change something else and try again.

I want to thank you for introducing me to a command I’d never seen before. That was where you suggested another option:

Code:

Set DataEntryItemsDBRn = .Cells(2, 1).CurrentRegion
MsgBox DataEntryItemsDBRn.Address

The term ‘CurrentRegion’ is a new one to me. I’ll check into it and see exactly what it means, though from looking at the whole line of code, I might make a guess. THANKS again for your help.

Why am I getting Error Message 1004?

TotallyConfused

Board Regular

Excel Facts

RoryA

MrExcel MVP, Moderator

TotallyConfused

Board Regular

MARK858

MrExcel MVP

TotallyConfused

Board Regular

MARK858

MrExcel MVP

RoryA

MrExcel MVP, Moderator

TotallyConfused

Board Regular

TotallyConfused

Board Regular

Similar threads

Forum statistics

Share this page

Why am I getting Error Message 1004?

Board Regular

Excel Facts

MrExcel MVP, Moderator

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

MrExcel MVP, Moderator

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock