Extract particular data from all sheets of a file into a new file

[karmen2017]

Hi there. I would need a help on the following issue: I have 6 giant excel files F1, F2, ... F6 , everyone containing at least 50 sheets. The data inside them is not structured at all and I need to make a centralized situation, the person before me was doing that manually. I cannot copy/paste the data into a new file F because there are merge cells and others... loose too much time looking for the areas to copy.
So i presumed that if i could copy from every sheet of every file F1, ..F6 a certain area (range of cells) indicating the first cell and the last one to a new working file F in the 1st sheet and extract only the values I need in 2nd sheet saves me time. The area to copy is dynamic, for example on file F1 from sheet1 I need to copy the area A200-Z230 , from sheet2 I need to copy the area A34-Z54, from sheet 3 I need to copy from A1234 to Z1250 and so on (can determine the first row and the last row from the area to be copied).
Don't know how to make the extraction, using the cells as parameters.
Or is there a better solution for the extraction?
Would much appreciate an idea / a solution.
Thank you in advance.

 

[jolivanes]

I don't know how long it will take to unmerge all the cells but if it makes life easier, it might be worth it.
If you want to run it on all workbooks in the same folder, code has to be adjusted for that.
I would try it on a copy of one of your workbooks first.

Sub Un_Merge_Cells()
Dim c As Range, i As Long
Dim lr As Long, lc As Long
Application.ScreenUpdating = False
    For i = 1 To ActiveWorkbook.Sheets.Count
        With Sheets(i)
            lr = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
            lc = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
                For Each c In .Range(.Cells(1, 1), .Cells(lr, lc))
                    With c
                        If .MergeCells Then
                            .MergeArea.UnMerge
                        End If
                    End With
                Next c
        End With
    Next i
Application.ScreenUpdating = True
End Sub

 

[karmen2017]

Hi Jolivanes.
Thank you, but unfortunatelly doesn't help me to unmerge the cells.

 

[karmen2017]

I can determine the last row number (nnnn) to be imported, from there i can determine the first row number (mmmm) to be imported, so I have the range .
Instead of having
......Windows("F1").Activate
Sheets("sheet1").Select
Rows("nnnn:mmmm").Select
Selection.copy
....
I would need a way of trasmitting the values from an array or a table in my working excel file.
Does anyone give me a hint, pls?
Thank you in advance.


....

 

[jolivanes]

Without an example workbook it is difficult to visualize what you have.
However, you said that in each sheet you can determine the first cell and last cell. How about the amount of columns?
If you assume that the first sheet is the sheet to copy into, you could use something like:

For i = 2 To Active Workbook.Sheets.Count
With Sheets(i)
mmmm =
nnnn = 
Set rng = .Range(.Cells(mmmm, 1), .Cells(nnnn, and here the last column number))
rng.copy Sheets("Master").Cells(rows.Count, 1).End(xlUp).Offset(1)
End With
Next i

 

[karmen2017]

Because the data is added on a daily basis, somehow i put a mark om the last row of every range to be imported. So I can determine the maximum nr of lines to be imported. I figured that using 6 arrays/tables with the line numbers to be imported woyld help me. So everyt day I just insert the new ranges and run the code again - see below from excel :

17.12.2017				18.12.2017
File	Sheet	First row	Last row		File	Sheet	First row	Last row
F1	Sheet1	200	220		F1	Sheet1	200	220
F1	Sheet1	244	264		F1	Sheet1	244	264
F1	Sheet2	34	54		F1	Sheet2	281	301
F1	Sheet3	1234	1254		F1	Sheet2	34	54
F1	Sheet3	1270	1290		F1	Sheet3	1234	1254
					F1	Sheet3	1270	1290
					F1	Sheet3	1300	1320
					F2	Sheet1	5	25
					F2	Sheet1	31	51

<colgroup><col span="9"></colgroup><tbody>
</tbody>

I am new to VBA and don't know yet how to handle this
Thank you.