Need Help Looking for errors
Results 1 to 7 of 7

Thread: Need Help Looking for errors
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Need Help Looking for errors

    Hello Everyone,

    I cant seem to find what is cauing my error.. I s a simpl code to on a file in the foldercopy a column ofdata nd pase in thedestination file on the next open column.

    Can someone help, maybe Ijus need more sleep, becaue I antsee it.

    Sub LoopThroughDirectory()
    Dim MyFile As String
    Dim ecol
    Dim Filepath As String

    Filepath = "C:\Users\HansoD02\Downloads\Desktop\Suppliers-master"
    MyFile = Dir(Filepath)
    Do While Len(MyFile) > 0
    If MyFile = "zmaster.xlsm" Then
    Exit Sub
    End If



    Workbooks.Open (Filepath & MyFile)
    Range("C6:C12").Copy
    ActiveWorkbook.Close




    ecol = Sheet1.Cells(2, Column.Count).End(xlToRight).Offset(1, 2).Column
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(1, ecol), Cells(1, ecol))

    MyFile = Dir
    Loop
    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Posts
    870
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need Help Looking for errors

    Is this working?

    You were missing a backslash from the path

    Code:
    Sub LoopThroughDirectory()
    Dim MyFile As String
    Dim ecol
    Dim Filepath As String
    
    
    Filepath = "C:\Users\HansoD02\Downloads\Desktop\Suppliers-master\"
    MyFile = Dir(Filepath)
    Do While Len(MyFile) > 0
    If MyFile = "zmaster.xlsm" Then
    Exit Sub
    End If
    
    
    
    
    
    
    Workbooks.Open (Filepath & MyFile)
    Range("C6:C12").Copy
    ActiveWorkbook.Close
    
    
    
    
    
    
    
    
    ecol = Sheet1.Cells(2, Column.Count).End(xlToRight).Offset(1, 2).Column
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(1, ecol), Cells(1, ecol))
    
    
    MyFile = Dir
    Loop
    End Sub
    Last edited by mrshl9898; Nov 1st, 2018 at 06:22 PM.

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help Looking for errors

    Thank you mrshl9898


    I put in the backslash but I gt ru time error 424
    Object required n this line

    ecol = Sheet1.Cells(2, Column.Count).End(xlToRight).Offset(1, 2).Column

    I als tried changing xlToLett

    ecol = Sheet1.Cells(2, Column.Count).End(xlToLeft).Offset(1, 2).Column

    Bt same error

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Posts
    870
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Need Help Looking for errors

    last column is:

    ecol = Sheets("Sheet1").Cells(2, Sheets("Sheet1").Columns.Count).End(xlToLeft).Column

    or with your offset

    ecol = Sheets("Sheet1").Cells(2, Sheets("Sheet1").Columns.Count).End(xlToLeft).Offset(1,2).Column

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,686
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need Help Looking for errors

    the 2nd one works for me...


    Code:
    Dim ecol as Integer
    ecol = Sheet1.Cells(2, Columns.Count).End(xlToLeft).Offset(1, 2).Column
    Last edited by Michael M; Nov 1st, 2018 at 09:00 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    New Member
    Join Date
    Nov 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Help Looking for errors

    ecol = Sheet1.Cells(2, Columns.Count).End(xlToLeft).Offset(1, 2).Column

    This worked
    BUT now I get the runtime error 1004
    We cant do that to a merged cell for this line

    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(1, ecol), Cells(1, ecol))

    Here is the curent code

    Sub LoopThroughDirectory()
    Dim MyFile As String
    Dim ecol As Integer
    Dim Filepath As String

    Filepath = "C:\Users\HansoD02\Downloads\Desktop\Suppliers-master"
    MyFile = Dir(Filepath)
    Do While Len(MyFile) > 0
    If MyFile = "zmaster.xlsm" Then
    Exit Sub
    End If



    Workbooks.Open (Filepath & MyFile)
    Range("C6:C12").Copy
    ActiveWorkbook.Close


    ecol = Sheet1.Cells(2, Columns.Count).End(xlToLeft).Offset(1, 2).Column


    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(1, ecol), Cells(1, ecol))

    MyFile = Dir
    Loop
    End Sub

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,686
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need Help Looking for errors

    Yep....merged cells and VBA simply don't mix !!
    You'll need to unmerge for this to work
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

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
  •