Importing several Output sheets into one consolidated Output Sheet

Hi all,

I have the following problem:

So I have 2 folders. An input and an output folder.

I regularly add and remove workbooks in/from the input folder.

Each workbook in the Input folder has its own input and output sheet.

In my output folder, I want to creat a single output workbook. This workbook should include a consolidated output sheet, which imports all output sheets (or a range (for example A10:Z30) of each sheet) from the input folder.

So far I just made several links between the output sheet of the output workbook and the output sheets of the input workbooks.

So it might say in cell A10 of the output sheet, output workbook:
"Import Range(A10:Z30) from the output sheet of input workbook(1)"

While in cell A35 of the same sheet (output sheet, output workbook) it would say:
"Import Range(A10:Z30) from the output sheet of input workbook(2)"

and so on...

This worked fine so far but is obviously limited as I always need to manually redefine all links once I remove or add another workbook in the input folder.

So what I would like VBA to do is:
"Find out how many workbooks there are in the input folder. For each workbook, open the output sheet, mark range(A10:Z30), copy and paste it in the output sheet of my output workbook in the output folder. Give it a headline/description (for example the title of the workbook it imported the output sheet from). Then, for every next range(A10:Z30) you copy and paste, go down 10 rows."

I hope I made myself clear, otherwise I would be happy to elaborate or up an example.

Thanks in advance guys, hope u can help me.

Take care,

Ugh Der ! :D

Hi
Save the attached file inside input folder. change sheet name of output sheet from sheet1 to whatever name you have (output) - Press Alt F11 to open macro window and look for sheet1 with comments. run the macro.
Ravi

Hi,

urm would it be possible to just post the code?

I will probably get in trouble, if I download and execute a file from an unknown source ^^

thx

HI
here is the code used. Save it in a workbook called Ugh.xls (inside input folder) and run the macro.

Please Login or Register  to view this content.

Ravi

Hi Ravi,

First off: Thank you so much for your help!

I was a bit busy these days (also with finding a solution myself) so I didn't have time to try out your code yet.

However I tested it just now and I think I kinda didn't explain clearly enough what I wanted to do

Maybe if I show you, what I came up with so far, it could help:

PHP Code: 

Sub Open_and_Import_All()
    Dim sFile As String, sPath As String
    Dim Wkb As Workbook, sTarget As Workbook
    Dim J As Integer, K As Integer
    
    Set sTarget= ThisWorkbook
    
    sTarget.Worksheets("Overview").Cells.Font.Name = "Calibri"
    sTarget.Worksheets("Overview").Cells.Font.Size = 11
    sTarget.Worksheets("Overview").Cells = ""

' clears the workbook "sTarget" from all previous content. _
'This is important as the Overview sheet will be "updated". _
'So every time I load new information, old information has_
'to be removed

    Application.ScreenUpdating = False
    sPath = "C:\test_input"
    If Right(sPath, 1) <> "/" Then
       sPath = sPath & "\"
    End If
    sFile = Dir(sPath & "*.xls")
    Do While sFile <> ""
       Workbooks.Open sPath & sFile

' this opens every .xls file that is found in the_
' "test_input" folder     

    J = J + 12
    K = J - 1
    Set Wkb = Workbooks.Open(sPath & sFile, False, True)
    Set sTarget = ThisWorkbook
    
    Wkb.Worksheets("Output").Range("A10:F20").Copy _
    Destination:=sTarget.Worksheets("Overview").Cells(J, 1)

' this copies the Range A10:F20 of each workbook's (Wkb) _
' "Output" sheet found in the test_input folder and pastes it _
'into the "Overview" sheet of the sTarget workbook. 12 rows _
'further down, it pastes the "Ouput" sheet of the next "Wkb" _
'found in the test_input folder and so forth until no more _
' "Wkb" are found in the folder   


    sTarget.Worksheets("Overview").Cells(K, 1).Hyperlinks.Add _
    Anchor:=sTarget.Worksheets("Overview").Cells(K, 1), _
    Address:=sFile, _
    TextToDisplay:=Wkb.Name

' this gives me the file name of each "Wkb" workbook in the _
'test_input folder and pastes it 1 row above the original entry, _
'as an identifier    


    Wkb.Close
    sFile = Dir()
    
    Loop
    
    Application.ScreenUpdating = True
    
 End Sub 


So that's what I found, collected, scrambled and came up with during the last days I believe you were working another angle, exporting it from the input-file instead if importing in the output-file, correct?

The "Wkb" workbooks in the test_input folder are project-files where information is entered and also includes a small output sheet.

The "sTarget" workbook will be my output file in which I have an "Overview" sheet that consolidates the output sheets of each project file. Here I want to open each "Wkb" I found in a folder and import a certain range of data from each into my "sTarget" "Overview" sheet.


It seems my coding was not very elegant to say the least and there seem to be many sources of error in it.

I just tried it out on the "real project workbooks" (the Wkb in the code) and came up with the following errors:

1) Once I ran the macro, it immediatly began complaining about links in the Wkbs.
I imagine this is due to the cells on the sheet "Output" in the Wkb being linked to the "Input" sheet of the Wkb (as in =Input!G33)

2) Some information was not imported properly. Some cells were showing zeroes (0) instead of a number that exists in the same cell of the source (WkB).
I imagine since in my code I miss a paste command in the form of your xlPasteSpecial format, so I will only transfer the values.

3) The folder "C:\test_input" has several sub-folders which are not being searched for files.
No need to imagine here, that's due to my lack of knowledge in coding, I don't know how I have to change my code to do this

4) The hyperlinks I create out of the Wkb.name for each found Wkb are shown as hyperlinks but if I click them, I get an error saying "The file couldn't be opened"
I imagine..... you know


So after this monster post and many words:

I really hope someone can help me and we will find the many wrongs in "my" code.

Take care y'all

Ugh_Der !

HI
The codes I provided lists all filenames in INPUT folder, opens each of them, copies Range A10to Z30 and pastes to Ugh.xls file, closes the input file without saving and the process repeats.Change sheet1 in my code to "overview" or whatever you wish to import and run it again. I have tested my code and it has worked with sheet1.
Ravi