copy many files into one

[Gabriel]

Hi,

I want to copy the usedranges from many files into one excel file. I
want those usedranges to be appended to the last column and not to the
last row. I thought that this part would do it, but it doesn't

Destination:=TargetSht.Cells(1, Columns.Count).End(xlLeft).Offset(0,
1)

Can anyone help please ?
Thank you
Gabriel

 

[Ron de Bruin]

Hi Gabriel

See this page for a example
http://www.rondebruin.nl/copy3.htm#column

Note: you only have 256 columns

 

[Ron de Bruin]

You can also use a function to find the last column with data
Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End FunctionSub test()
MsgBox Lastcol(ActiveSheet)
End Sub

 

[Ron de Bruin]

This is a mess<g>

Try this

You can also use a function to find the last column with data

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Sub test()
MsgBox Lastcol(ActiveSheet)
End Sub