C&P all data of all the sheets/workbooks in a folder.

[countryfan_n]

Hello friends,

I have a folder named 'TheReport', and it has all sort if excel workbooks.

I need a VBA code in workbook named: 'Summary' / Sheetname: 'C.All'.

The code will:
1. Go thru the workbooks and worksheets (of the workbooks) ranges A1:A1000.

2. Copy and paste anything in the cells of these sheets, as long as there is data to copy.

3. And paste all the copied data on sheet C.All in workbook 'Summary'.

Is that possible, if yes, would you please / kindly help me with the code?

Thank you very much in advance.
Nawaf

[Bob Phillips]

Here is some folder loop code




Dim oFSO

Sub LoopFolders()

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
End If
Next file

End Sub

[countryfan_n]

Hi & thanks for the reply, I am having a problem, the code opens all the Excel files of the folder instead of copying them and pasting the contents of the sheets to sheet "C.All".

Please see the below points, it should explain:
1. Go thru the workbooks and worksheets (of the workbooks) ranges A1:A1000.

2. Copy and paste anything in the cells of these sheets, as long as there is data to copy.

3. And paste all the copied data on sheet 'C.All' in workbook 'Summary'.


Thanks again, and truly appreciate it,
Nawaf

[Bob Phillips]

As I said, it was the folder lopop code. You need to opne them to do the copy and paste. I left that bit for your completion.

[countryfan_n]

Hi again, and thanks!

I baiscally added the codes that would actually copy and paste the data from the sheets.

However, my codes only capture the sheets that were last opened. I mean when a workbook is opened, and you have 3 sheets in it, it will only copy the sheet that is opened. I dont want to only copy that. I want the code to copy the contenets of all the 3 sheets of any open workbook.

I appreciate your help in your 1st code, but would you kindly help me tweak the code that I made.

Thanks,
Nawaf