Ramesh Deo
Member
HI Luke,
Could you please help me at urgent basis.
Could you please help me at urgent basis.
Sub test()
Dim sPath As String
Dim sFil As String
Dim strName As String
Dim twbk As Workbook
Dim owbk As Workbook
Dim ws As Worksheet
Set twbk = ActiveWorkbook
sPath = "C:\yyyyyyyy\"
sFil = Dir(sPath & "*.xls")
Do While sFil <> ""
strName = sPath & sFil
Set owbk = Workbooks.Open(strName)
Set ws = owbk.Sheets(1)
ws.Range("A1", ws.Range("E" & Row.Count).End(xlUp)).Copy twbk.Sheets(1).Range("A" & Rows.Count).End(xlUp)(2)
owbk.Close False
sFil = Dir
Loop
twbk.Save
End Sub
Sub test()
Dim sPath As String
Dim sFil As String
Dim strName As String
Dim twbk As Workbook
Dim owbk As Workbook
Dim ws As Worksheet
Set twbk = ActiveWorkbook
sPath = "C:\yyyyyyyy\"
sFil = Dir(sPath & "*.xls")
Do While sFil <> ""
strName = sPath & sFil
Set owbk = Workbooks.Open(strName)
'Loop through all possible worksheets
For Each ws In owbk.Worksheets
ws.Range("A1", ws.Range("E" & Rows.Count).End(xlUp)).Copy twbk.Sheets(1).Range("A" & Rows.Count).End(xlUp)(2)
Next ws
owbk.Close False
sFil = Dir
Loop
twbk.Save
End Sub
We can add a quick loop to go through all the worksheets.
Code:Sub test() Dim sPath As String Dim sFil As String Dim strName As String Dim twbk As Workbook Dim owbk As Workbook Dim ws As Worksheet Set twbk = ActiveWorkbook sPath = "C:\yyyyyyyy\" sFil = Dir(sPath & "*.xls") Do While sFil <> "" strName = sPath & sFil Set owbk = Workbooks.Open(strName) 'Loop through all possible worksheets For Each ws In owbk.Worksheets ws.Range("A1", ws.Range("E" & Rows.Count).End(xlUp)).Copy twbk.Sheets(1).Range("A" & Rows.Count).End(xlUp)(2) Next ws owbk.Close False sFil = Dir Loop twbk.Save End Sub