Run time error

gheyman

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,341
Office Version
  1. 365
Platform
  1. Windows
I am getting an error message when running this code

Run-time error '1004':
Select method of Range class failed

This is the line of code:

Sheets("DM Cost Source Details").Range("D2:E" & IDLR).Select

Code: 
Sub Execute()

'On Error Resume Next

'*********************************
'Hide Other Tabs
'********************************

Application.ScreenUpdating = True

    Sheets("Step 1").Visible = xlSheetVeryHidden
    Sheets("Step 2").Visible = xlSheetVeryHidden
    Sheets("Step 3").Visible = xlSheetVeryHidden
    Sheets("Step 4").Visible = xlSheetVeryHidden
    Sheets("Step 5").Visible = xlSheetVeryHidden
    Sheets("Step 7").Visible = xlSheetVeryHidden
    Sheets("SSJ").Visible = xlSheetVeryHidden
    Sheets("Parts").Visible = xlSheetVeryHidden
    Sheets("Cost Sources").Visible = xlSheetVeryHidden
    Sheets("Cost Source Details").Visible = xlSheetVeryHidden
    Sheets("Vendors").Visible = xlSheetVeryHidden

Application.ScreenUpdating = False
      
            UserForm3.Show vbModeless
            UserForm3.LabelRetrieve.Width = 0
            UserForm3.LabelTransform.Width = 0
            UserForm3.LabelGenerate.Width = 0




            UserForm3.LabelProg.Width = 20
            UserForm3.LabelProg.Caption = "5%"
            UserForm3.LabelRetrieve.Width = 48
            DoEvents
            
                        

      
    Sheets("DM Parts").Visible = True
    Sheets("DM Cost Sources").Visible = True
    Sheets("DM Cost Source Details").Visible = True
    Sheets("DM Vendors").Visible = True
    Sheets("Other Tables").Visible = True
    

                UserForm3.LabelProg.Width = 35
                UserForm3.LabelProg.Caption = "10%"
                DoEvents
                
'***********************************
'Run Query Refresh
'***********************************

        'Refresh Query
            With ThisWorkbook
                Sheets("DM Parts").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            End With
            
                UserForm3.LabelProg.Width = 45
                UserForm3.LabelProg.Caption = "30%"
                UserForm3.LabelTransform.Width = 54
                DoEvents
            
        'Refresh Query
            With ThisWorkbook
                Sheets("DM Cost Sources").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            End With
            
                UserForm3.LabelProg.Width = 55
                UserForm3.LabelProg.Caption = "35%"
                DoEvents
            
        'Refresh Query
            With ThisWorkbook
                Sheets("DM Cost Source Details").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            End With
            
                UserForm3.LabelProg.Width = 60
                UserForm3.LabelProg.Caption = "40%"
                DoEvents
            
        'Refresh Query
            With ThisWorkbook
                Sheets("DM Vendors").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            End With
            
                UserForm3.LabelProg.Width = 65
                UserForm3.LabelProg.Caption = "45%"
                DoEvents
      
        'Refresh Query
            With ThisWorkbook
                Sheets("Step 7").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
            End With
            
                UserForm3.LabelProg.Width = 75
                UserForm3.LabelProg.Caption = "50%"
                UserForm3.LabelGenerate.Width = 48
                DoEvents
                
                
        'Set the ID and Rev on both the DM Cost Sources and DM Cost Source Details
    
            Dim IDLR As Long
    
                IDLR = Sheets("DM Cost Source Details").Cells(Rows.Count, "A").End(xlUp).Row
                If IDLR > 2 Then
            
                    Sheets("DM Cost Sources").Range("D2:E2").Copy
                    Sheets("DM Cost Source Details").Range("D2:E" & IDLR).Select
            
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
    
                    Application.CutCopyMode = False
        
                End If
      
'*****************************************************
' Clear Previous data from ProPricer Templates if any
'*****************************************************
    
    'Clear Template
            VLR = Sheets("Vendors").Cells(Rows.Count, "A").End(xlUp).Row
            If VLR > 2 Then Sheets("Vendors").Range("A3:U" & VLR).ClearContents
            
            
                UserForm3.LabelProg.Width = 80
                UserForm3.LabelProg.Caption = "53%"
                DoEvents
            
     'Clear Template
            CSLR = Sheets("Cost Sources").Cells(Rows.Count, "A").End(xlUp).Row
            If CSLR > 2 Then Sheets("Cost Sources").Range("A3:AC" & CSLR).ClearContents
            
                UserForm3.LabelProg.Width = 90
                UserForm3.LabelProg.Caption = "59%"
                DoEvents
            
    'Clear Template
            CSDLR = Sheets("Cost Source Details").Cells(Rows.Count, "A").End(xlUp).Row
            If CSDLR > 2 Then Sheets("Cost Source Details").Range("A3:I" & CSDLR).ClearContents
            
                UserForm3.LabelProg.Width = 100
                UserForm3.LabelProg.Caption = "61%"
                DoEvents
            
    'Clear Template
            PLR = Sheets("Parts").Cells(Rows.Count, "A").End(xlUp).Row
            If PLR > 2 Then Sheets("Cost Source Details").Range("A3:AA" & PLR).ClearContents
            
                UserForm3.LabelProg.Width = 110
                UserForm3.LabelProg.Caption = "65%"
                DoEvents
                
                
'*****************************************************
' Transfer data from DM tabs to ProPricer templates
'*****************************************************

        'Transfer Data
            Sheets("DM Parts").ListObjects("DM_Parts").DataBodyRange.Copy Sheets("Parts").Range("A3")
            
                UserForm3.LabelProg.Width = 120
                UserForm3.LabelProg.Caption = "70%"
                DoEvents
            
            
        'Transfer Data
            Sheets("DM Cost Source Details").ListObjects("DM_Cost_Source_Details").DataBodyRange.Copy Sheets("Cost Source Details").Range("A3")
            
            
                UserForm3.LabelProg.Width = 130
                UserForm3.LabelProg.Caption = "72%"
                DoEvents
            
        'Transfer Data
            Sheets("DM Cost Sources").ListObjects("DM_Cost_Sources").DataBodyRange.Copy Sheets("Cost Sources").Range("A3")
            
                UserForm3.LabelProg.Width = 140
                UserForm3.LabelProg.Caption = "73%"
                DoEvents
            
        'Transfer Data
            Sheets("DM Vendors").ListObjects("DM_Vendors").DataBodyRange.Copy Sheets("Vendors").Range("A3")
            
                UserForm3.LabelProg.Width = 150
                UserForm3.LabelProg.Caption = "75%"
                DoEvents
                
                

    


'**************************************************************************************************



    Sheets("Parts").Visible = True
    Sheets("Cost Sources").Visible = True
    Sheets("Cost Source Details").Visible = True
    Sheets("Vendors").Visible = True
    
    Sheets("DM Parts").Visible = xlSheetVeryHidden
    Sheets("DM Cost Sources").Visible = xlSheetVeryHidden
    Sheets("DM Cost Source Details").Visible = xlSheetVeryHidden
    Sheets("DM Vendors").Visible = xlSheetVeryHidden
    Sheets("Other Tables").Visible = xlSheetVeryHidden
    Sheets("Selected Parts List").Visible = xlSheetVeryHidden

    
  
    UserForm3.Hide
    
            Sheets("Step 6").Select
        ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
        Selection.ShapeRange.Fill.Visible = msoFalse
        Range("F3").Select
        
        MsgBox "Query has been succesfully executed. Please validate the data on the templates and then Export to ProPricer"
    


Application.ScreenUpdating = True

    

      
      
End Sub

If I run this code separately it works fine, Its only when I inserted it into the above code did I start getting the error

Dim IDLR As Long

IDLR = Sheets("DM Cost Source Details").Cells(Rows.Count, "A").End(xlUp).Row
If IDLR > 2 Then

Sheets("DM Cost Sources").Range("D2:E2").Copy
Sheets("DM Cost Source Details").Range("D2:E" & IDLR).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

End If


Any help is appreciated
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
A few things:

1. What is the value of "IDLR" when you get this error?
You can simply add a Message Box just above that line to find out, i.e.:
VBA Code: 
MsgBox IDLR

2. Are you on the "DM Cost Source Details" when that line of code hits?
I don't think you can select a range for a sheet that you are not on.
You need to select the sheet first, if you want to select a range on that sheet.

3. "Execute" is a reserved word, and you should never use reserved words as names of your procedures or variables!
 
Upvote 0
Thanks, Joe

I will change the name.

No I am not on DM Cost Source Details when the code hits that line. Is there another way to write this so that I do not have to be on that worksheet?
 
Upvote 0
You don't need to select the range first. You can just copy to it directly.
Try this:
VBA Code: 
                    Sheets("DM Cost Sources").Range("D2:E2").Copy
                    Sheets("DM Cost Source Details").Range("D2:D" & IDLR).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
Also note that in your paste statement, you only specify the FIRST column to paste to (notice the slight change I made to the paste range).
Since you are copying a two column range, it will automatically spill over to the next column.
You just need to tell it the left-most column to paste to.
 
Upvote 0
Solution
You are welcome.
 
Upvote 0
You must log in or register to reply here.

Similar threads

gheyman
  • Solved
VB Run-time error '91':
Replies
4
Views
557
Fluff
Fluff
gheyman
  • Locked
  • Solved
Code not pasting a cell
Replies
2
Views
351
Peter_SSs
Peter_SSs
gheyman
  • Solved
code not pasting into one cell
Replies
4
Views
415
gheyman
gheyman
gheyman
  • Question
UserForm3.Show VBA Run-time error 424
Replies
2
Views
655
RoryA
RoryA
gheyman
  • Solved
Userform ListBox not placing all the selection into the table
Replies
3
Views
476
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,952
Messages
6,122,457
Members
449,083
Latest member
Ava19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top