Error 1004

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Error 1004

  1. #1
    New Member
    Join Date
    Jan 2017
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Error 1004

     
    The following code works when I execute it from the WB workbook but returns error 1004 when I run it from WBi workbook I need to run it from WBi Woorkbook. Thx for the help in advance

    Code:
    Option Explicit
    
    Sub MatchCopy()
    
    Dim WB As Workbook            
    Dim WBi As Workbook            
    Dim FindString1 As Long    
    Dim FindString2 As Long     
    Dim Lcol1 As Long             
    Dim Lcol2 As Long           
    Dim Lrow1 As Long              
    Dim Lrow2 As Long               
    
    Application.ScreenUpdating = False
    
    Set WBi = Workbooks("WBi.xlsm")
    Set WB = Workbooks("WB.xlsx")
    
    
    WBi.Sheets("Sheet1").Range("A1:B180").Clear
    
    FindString1 = "104"
    FindString2 = "301"
    
    With WB.Sheets("Sheet1")
    
        Lcol1 = Application.Match(FindString1, .Rows(27), 0)
    
        Lcol2 = Application.Match(FindString2, .Rows(6), 0)
    
        Lrow1 = Cells(Rows.count, Lcol1).End(xlUp).Row
        Lrow2 = Cells(Rows.count, Lcol2).End(xlUp).Row
    
        .Range(Cells(27, Lcol1), Cells(Lrow1, Lcol1)).Copy WBi.Sheets("Sheet1").Range("A1")
        .Range(Cells(6, Lcol2), Cells(Lrow2, Lcol2)).Copy WBi.Sheets("Sheet1").Range("B1")
    
    End With
    
    Application.ScreenUpdating = True
    
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,242
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Error 1004

    See if this works (UNTESTED)

    Observe the .
    Code:
    Sub MatchCopy()
    
    Dim WB As Workbook
    Dim WBi As Workbook
    Dim FindString1 As Long
    Dim FindString2 As Long
    Dim Lcol1 As Long
    Dim Lcol2 As Long
    Dim Lrow1 As Long
    Dim Lrow2 As Long
    
    Application.ScreenUpdating = False
    
    Set WBi = Workbooks("WBi.xlsm")
    Set WB = Workbooks("WB.xlsx")
    
    WBi.Sheets("Sheet1").Range("A1:B180").Clear
    
    FindString1 = "104"
    FindString2 = "301"
    
    With WB.Sheets("Sheet1")
        Lcol1 = Application.Match(FindString1, .Rows(27), 0)
        Lcol2 = Application.Match(FindString2, .Rows(6), 0)
    
        Lrow1 = .Cells(.Rows.Count, Lcol1).End(xlUp).Row
        Lrow2 = .Cells(.Rows.Count, Lcol2).End(xlUp).Row
    
        .Range(.Cells(27, Lcol1), .Cells(Lrow1, Lcol1)).Copy WBi.Sheets("Sheet1").Range("A1")
        .Range(.Cells(6, Lcol2), .Cells(Lrow2, Lcol2)).Copy WBi.Sheets("Sheet1").Range("B1")
    End With
    Application.ScreenUpdating = True
    End Sub
    Hope this helps

    M.
    Last edited by Marcelo Branco; May 22nd, 2017 at 11:27 AM.

  3. #3
    New Member
    Join Date
    Jan 2017
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Error 1004

    Wow It works thank you ,but why it worked when was executed from the WB workbook and what the dots do in this case?

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    13,242
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Error 1004

      
    You are welcome. Glad to help.

    You must qualify (specify) the ranges properly inside the With...End With exactly like you did to get LCol1 and LCol2 using .Rows(27) and .Rows(6) .
    Without the dots VBA considers the ranges on the ActiveSheet, not on WB.Sheets("Sheet1").

    see
    Referring to Ranges in VBA | Excel Matters

    M.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

  

 

 

DMCA.com