merging different sheets into one with the same structure

Asked Nov 16 '20 at 19:20

Active Nov 16 '20 at 19:20

Viewed 39 times

I Have some sheets and a "Consolidated Pipeline" sheet in which i have to merge all the other sheets. I just need to copy all the data from every sheet in the consolidated one, one after the other.

I have a problem with the function that find the last non-empty row to understand where to append the data in the consolidated sheet but it seems that the last row returned is always the very last row of the document, not the last non-empty one

this is the function

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(what:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row                          
    On Error GoTo 0
End Function

asked Nov 16 '20 at 19:20

Syertim

What is your data like? Are there formulas that return a blank string `""`? – BigBen Nov 16 '20 at 19:22
Yes, some formula could return string "" – Syertim Nov 16 '20 at 19:30
Note that your code approach considers any cells that are populated, which would include cells with formulas returning a blank string. – BigBen Nov 16 '20 at 19:31
How can I avoid those cells? – Syertim Nov 16 '20 at 19:32
1

[One approach here](https://stackoverflow.com/questions/35486695/how-do-i-find-the-last-row-that-contains-data-where-cells-may-contain-empty-text). – BigBen Nov 16 '20 at 19:37
1

[Another here](https://stackoverflow.com/questions/33971250/find-the-last-not-empty-row-in-a-range-of-cells-holding-a-formula). – BigBen Nov 16 '20 at 19:39

merging different sheets into one with the same structure

0 Answers0