Copy and Merge Selected Range from Different Worksheets and Workbooks

[knighcloud]

I have three workbooks, each workbook contains a total of 105 sheets. Only 5 has a different format. The rest of the sheets are the same. What I'm hoping to do is to copy and/or merge the data from all sheets into one sheet in a separate workbook. Since all sheets have the same format, I would like to copy the header from the first sheet only and the rest would just be data. Each worksheet name starts with "Plot". Also, if the next row in the worksheet is blank, then proceed to next sheet to copy data and so on. I've tried several macro already, but since I'm a beginner in VB, I was not able to customize it to suit my need. Any suggestions? Thank you.

https://docs.google.com/leaf?id=0B2AH1L7bXPyhMGE5ZDdhMjYtZTY2ZC00ZWZjLWE3NGMtN2NlMzNjNTRhMThk&hl=en

 

[kpark91]

First of all, it's not enough information.
Second of all, are you sure you want to merge 105 sheets?
Third of all, this directly relates to the product they're selling in MrExcel, so I don't think MrExcel MVP's will answer
Last of all, I've tried working on it and failed lol. I had to do it semi-automatically in the end but with only 2 sheets and it was annoying -_-

 

[knighcloud]

I understand that this is quite hard to explain. I have read some of the posts here regarding merging worksheets and workbooks. But, like I said, I wasn't able to apply as solution to my problem. I'm not sure if the solution that was posted on other threads apply to numerous worksheets. The macros on merging data actually worked when I tested it but I can't seem to make it work on my workbook.

 

[kpark91]

It's extremely hard to explain. I know.cuz merging worksheets was the reason I really joined this forum although nobody answered in the end. -_-

It is extremely hard to make a general code for merging any worksheets.

So, in the end, it has to be done with little bit of brainpower and little tricks here and there (takes long still -_-)

 

[knighcloud]

I'm actually trying some macros again. Here's one that I've tried so far which merges all data on every worksheet of the workbook:


Sub MergeSheets()
Const sRANGE = A3:m35
Dim iSheet, iTargetRow As Long, oCell As Object, bRowWasNotBlank As Boolean
Dim iTop, iLeft, iBottom, iRight As Long
'Sheets(1).Select: Sheets.Add
Sheets(1).Select
Cells.Select
Selection.Clear
bRowWasNotBlank = True
For iSheet = 2 To ThisWorkbook.Sheets.Count: DoEvents
For Each oCell In Sheets(iSheet).Range(sRANGE).Cells: DoEvents
If oCell.Column = 1 Then
If bRowWasNotBlank Then iTargetRow = iTargetRow + 1
bRowWasNotBlank = False
End If
If oCell.MergeCells Then
bRowWasNotBlank = True
If oCell.MergeArea.Cells(1).Row = oCell.Row Then
If oCell.MergeArea.Cells(1).Column = oCell.Column Then
Sheets(1).Cells(iTargetRow, oCell.Column) = oCell
iTop = iTargetRow
iLeft = oCell.Column
iBottom = iTop + oCell.MergeArea.Rows.Count - 1
iRight = iLeft + oCell.MergeArea.Columns.Count - 1
Sheets(1).Range(Cells(iTop, iLeft), Cells(iBottom, iRight)).MergeCells = True
End If
End If
End If
If Len(oCell) Then bRowWasNotBlank = True
Sheets(1).Cells(iTargetRow, oCell.Column) = oCell
Next oCell
Next
Sheets(1).Activate
End Sub

It was able to merge all the data, but what I wanted was to have the header of the first sheet only. The rest would just be data copied from the original data. This will merge the data in a different worksheet on the same workbook.