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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,971
Messages
5,508,471
Members
408,688
Latest member
Bhojraj

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top