loop

[eran3185]

hi

i have 30 files : *.xls

i want to do a loop that take the 1-200 lines from all the files , and put them in one file

how can i do this thing ?

 

[stanleydgromjr]

eran3185,

This should get you started in the right direction.

See this link for a solution by 'Jindon':

[url]http://www.ozgrid.com/forum/showthread.php?t=64943
[/url]

Have a great day,
Stan

 

[eran3185]

i dont know why it didnt work

 

[Norie]

eran

Is the code in the link Stan posted no use?

If it is it might help if you told us how.

 

[eran3185]

first of all - i know very little about vba

i think that u and stan mean this part :
Sub teset()
Dim myDir As String, fn As String
myDir = "C:\test\" '<- change to suite
fn = Dir(myDir & "TM08e*.xls")
Do While fn <> ""
With Workbooks.Open(myDir & fn)
.Sheets("test suite").Copy
.Close False
End With
fn = Dir()
Loop
End Sub

but it wasnt good , i dont know what the meaning of "TM08e*.xls" , and what is all the variables

 

[stanleydgromjr]

eran3185,

What is the full path to where the workbooks are stored?

What is the sheetname that you want to copy from in all the workbooks?

What is the range, in the above sheetname, of the information you want to copy?

What is the workbook name, and sheetname, where you want to copy the information to?

Have a great day,
Stan

 

[eran3185]

What is the full path to where the workbooks are stored? c:/test

What is the sheetname that you want to copy from in all the workbooks? big is the summing workbook

What is the range, in the above sheetname, of the information you want to copy? a1:n100

What is the workbook name, and sheetname, where you want to copy the information to? i have some workbooks tat i want to copy to "big" , the name are : book1 , book2 , book3 .
the name of the sheetname is "7.07" in all of the workbooks

many many thanks

 

[eran3185]

stan , maybe u can explein me this ??
Sub teset()
Dim myDir As String, fn As String, a(), i As Integer, ws As Worksheet, LastR As Range
myDir = "C:\test\" '<- change to suite
fn = Dir(myDir & "TM08e*.xls")
Do While fn <> ""
With Workbooks.Open(myDir & fn)
.Sheets(1).Copy after:=ThisWorkbook.Sheets(1)
With ThisWorkbook.Sheets(1)
Set LastR = .Range("a40")
If Not IsEmpty(LastR) Then _
Set LastR = .Range("a" & Rows.Count).End(xlUp).Offset(1)
End With
With .Sheets(2).UsedRange
LastR.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
.Close False
End With
fn = Dir()
Loop