I am writing code which
loops through the files in a folder,
points at sheet 1 of each file,
copies all the non-empty cells in column B of each sheet 1 (and the rows),
pastes this to consolidate worksheet.

so if i have 4 wks of 20 rows each and 1 wks of 30 rows,
"consolidate" will have 110 rows,
the code i have is close, however it only seems to be copying first row.
Any help is greatly appreciated, thanks


Sub SubGetMyData3()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim owb As Workbook
Dim i, j As Long

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\My Documents\Career")
i = 1
j = 1
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then

Set owb = Workbooks.Open(Filename:=objFolder.Path & "\" &
objFile.Name)
owb.Worksheets("Sheet1").Cells(i, 2).EntireRow.Copy
Destination:=Worksheets("consolidate").Cells(j, 1)
i = owb.Worksheets("sheet1").Cells(Rows.Count, "A").Row + 1
j = Worksheets("consolidate").Cells(Rows.Count,
"A").End(xlUp).Row + 1
ActiveWorkbook.Close savechanges:=True
End If
Next
End Sub