Copy and Merge Selected Range from Different Worksheets and Workbooks

knighcloud

New Member
Joined
Jul 20, 2010
Messages
13
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
First of all, it's not enough information.
Second of all, are you sure you want to merge 105 sheets? o_O
Third of all, this directly relates to the product they're selling in MrExcel, so I don't think MrExcel MVP's will answer o_O
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

New Member
Joined
Jul 20, 2010
Messages
13
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

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
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

New Member
Joined
Jul 20, 2010
Messages
13
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,639
Messages
5,660,028
Members
418,543
Latest member
alreadybeen23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top