Need Help Looking for errors

[proe0229]

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

 

[mrshl9898]

Is this working?

You were missing a backslash from the path

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

 

[proe0229]

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

 

[mrshl9898]

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

 

[Michael M]

the 2nd one works for me...

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

 

[proe0229]

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

 

[Michael M]

Yep....merged cells and VBA simply don't mix !!
You'll need to unmerge for this to work