×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

add all the files under the same folder including the subfolder, all sheets in worbook included

add all the files under the same folder including the subfolder, all sheets in worbook included

add all the files under the same folder including the subfolder, all sheets in worbook included

(OP)
I have another question, the following is the vba code, but could not find the files under the sub-folder, the code could only find and copy the specific datas from "cost"(sheet name) in the excel workbook files under the same folder, but could not copy the data from the files under the sub-folder in this folder.
and what's more, it could only copy all the data from the sheetname is "cost", if i wannna copy all the sheets(with same structure) from every workbook under the same folder including sub-folder, how to amend the code. thanks for your help.
file = Dir(folderPath & "\*.xls*")
Do While file <> ""

If Not file = ThisWorkbook.Name Then
Set wb = Workbooks.Open(folderPath & "\" & file)
With wb.Sheets("cost")
Set costRange = .Range("A4:D15")
Set dataRange = .Range("G4:I15")
lastRow = ThisWorkbook.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row + 1
erow = 0 '

For Each Row In costRange.Rows
If Not IsEmpty(Row.Cells(1, 1)) Then

ThisWorkbook.Sheets(1).Cells(lastRow, 15) = wb.Name
ThisWorkbook.Sheets(1).Cells(lastRow, 2) = Row.Cells(1, 1)
'ThisWorkbook.Sheets(1).Cells(lastRow, 2).Resize(, 3) = Row.Value
ThisWorkbook.Sheets(1).Cells(lastRow, 11) = Row.Cells(1, 3)
ThisWorkbook.Sheets(1).Cells(lastRow, 12) = Row.Cells(1, 4)
lastRow = lastRow + 1

End If
Next
For Each Row In dataRange.Rows
If Not IsEmpty(Row.Cells(1, 1)) Then

ThisWorkbook.Sheets(1).Cells(lastRow, 15) = wb.Name
ThisWorkbook.Sheets(1).Cells(lastRow, 2).Value = Row.Cells(1, 1)
ThisWorkbook.Sheets(1).Cells(lastRow, 11) = Row.Cells(1, 2)
ThisWorkbook.Sheets(1).Cells(lastRow, 12) = Row.Cells(1, 3)
lastRow = lastRow + 1

End If
Next
End With
wb.Close SaveChanges:=False
End If
file = Dir
Loop


From the subjected code, I could only copy the data from every workbook in the same folder, but not including the sub-folders, and the copying action is just copy the sheet name is "cost"now, if I wanna to copy all the shhets with same struction in every workbook also, how to amend the code, thanks for your help.

RE: add all the files under the same folder including the subfolder, all sheets in worbook included

(OP)
thnks COMBO, I WILL TRY TO SOLVE

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close