VBA code for combine sheet variant needed

[excelsishya]

Hi Guys,

I am looking for VBA code for combine sheet variant.

If Workbook as multiple Tabs, and in group mode if i select say 7 rows 4 columns , in first tab, i want all tabs 7 rows 4 columns combined in new master sheet below one another and with respective tab name in one of blank Right column in master sheet. Please see sample below for what i am looking for.
Thanks

Tab 1(BS2009)
<table width="309" border="0" cellpadding="0" cellspacing="0"><col style="width: 77pt;" width="103"> <col style="width: 59pt;" width="78"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 77pt;" width="103" height="20">BALANCE SHEET</td> <td style="width: 59pt;" width="78">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Description</td> <td>code</td> <td>amount</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>A</td> <td align="right">1</td> <td align="right">500</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>A</td> <td align="right">1</td> <td align="right">802</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>A</td> <td align="right">1</td> <td align="right">2581</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>A</td> <td align="right">1</td> <td align="right">2963</td> </tr> </tbody></table>TAB2(BS2010)
<table width="295" border="0" cellpadding="0" cellspacing="0"><col style="width: 77pt;" width="103"> <col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 77pt;" width="103" height="20">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Description</td> <td>code</td> <td>amount</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>B</td> <td align="right">2</td> <td align="right">1000</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>C</td> <td align="right">3</td> <td align="right">1000</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>C</td> <td align="right">3</td> <td align="right">1000</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>C</td> <td align="right">3</td> <td align="right">1000</td> </tr> </tbody></table>COMBINED SHEET TAB OUTPUT DESIRED
<table width="384" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="6"> <tbody><tr style="height: 15pt;" height="20"> <td colspan="2" style="height: 15pt; width: 96pt;" width="128" height="20">BALANCE SHEET</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">2009BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>2009BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Description</td> <td>code</td> <td>amount</td> <td>
</td> <td>2009BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>A</td> <td align="right">1</td> <td align="right">500</td> <td>
</td> <td>2009BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>A</td> <td align="right">1</td> <td align="right">802</td> <td>
</td> <td>2009BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>A</td> <td align="right">1</td> <td align="right">2581</td> <td>
</td> <td>2009BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>A</td> <td align="right">1</td> <td align="right">2963</td> <td>
</td> <td>2009BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>2010BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>2010BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Description</td> <td>code</td> <td>amount</td> <td>
</td> <td>2010BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>B</td> <td align="right">2</td> <td align="right">1000</td> <td>
</td> <td>2010BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>C</td> <td align="right">3</td> <td align="right">1000</td> <td>
</td> <td>2010BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>C</td> <td align="right">3</td> <td align="right">1000</td> <td>
</td> <td>2010BS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>C</td> <td align="right">3</td> <td align="right">1000</td> <td>
</td> <td>2010BS</td> </tr> </tbody></table>
<table width="309" border="0" cellpadding="0" cellspacing="0"><col style="width: 77pt;" width="103"> <col style="width: 59pt;" width="78"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td style="width: 59pt;" width="78">
</td><td style="width: 48pt;" width="64">
</td><td style="width: 48pt;" width="64">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td>
</td><td>
</td><td>
</td></tr></tbody></table>

 

[venkat1926]

the first sheet name is "BS2009" and the second sheet name is "BS2010"
there is a "sheet3" is available

the data in the first two sheets are from A1 to down and to right with hedings in the row no. 1

in that case try this macro test

Sub test()
Dim r As Range, j As Integer, k As Integer, dest As Range
j = Worksheets.Count
For k = 1 To j
If Worksheets(k).Name = "Sheet3" Then GoTo nextk
With Worksheets(k)
Set r = Range(.Range("A2"), .Range("A2").End(xlToRight).End(xlDown))
r.Copy
With Worksheets("sheet3")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest.PasteSpecial
Range(dest.End(xlToRight).Offset(0, 1), dest.End(xlDown).End(xlToRight).Offset(0, 1)).FormulaArray = _
    Right(Worksheets(k).Name, 4) & Left(Worksheets(k).Name, 2)
End With
End With
nextk:
Next k
Worksheets(1).Range("A1").EntireRow.Copy Worksheets("sheet3").Range("A1")

End Sub

 

[excelsishya]

Hi Venkat ,

Thanks for code.

Code works perfectly when there is 2 tab and sheet 3 is available. however when i try on new workbook with multiple tabs or insert new sheet to existing book it gives me run time error 9 and sometimes copy paste area is not same error.

Apologies for being not clear in my earlier post about what i was looking for.

On any given workbook with multiple tabs(20,30 may vary) when i selected say 10 tabs in group mode and select particular range of cell eg A3:E50 in any of grouped tabs and run code , each of this range should be copied and pasted in newly created tab called "combined sheet" one below other and thier tab name in any of blank right column say in above eg f column.

Is it possible modify your above code to meet this requirement? i feel your code is very close to my requirement.

Thanks for the help.

 

[venkat1926]

i SUPPOSE the macro will work more than two sheets. In my macro sheet3 is the summary sheet . no data is there in sheet3 in the beginning. data is there only in sheet1, sheet2,sheet4, sheet 5 etc. you again try.
if you want you rename sheet3 as "summary" without quotation marks. in the macro replace "sheet3" by "summary". both the summary sheet and other sheets where data are there can have any names must be available in the workbook.


see the statement
j=worksheets.count
this means that j is the number of sheets in the workbook

and then the macro loops though all the sheets except sheet3.

If there is a problem attach a workbook with three sheets (with very small amount of data) and a summary(sheet)

 

[excelsishya]

Hi ,

Here sample workbook link

http://www.4shared.com/document/Av1nax5N/SAMPLE_SHEET.html

It has 3 Tabs .By selecting A1: F100 in grouped mode in 1 of tab and run the code all 3 tabs A1:F100 should be copied pasted in summary sheet.

Should i create new tab name it sheet 3/summary sheet?. is it not possible for code create new sheet(summary sheet) and copy data?

Thanks

 

[venkat1926]

it is possible to insert a NEW sheet summary and copy in that sheet by modifying the macro. Next time when you run the macro again it will try to insert another NEW sheet with the same name and get confused. Of course I can write code so that first time it will insert the summary sheet and subsequent time if summary sheet is already available it will skip those steps.
do you want it. is it not easier just insert "summary' sheet and then use the macro unless there is some problem for you in this procedure.

I was not able to download your file beside it appears to be not excel format.

There are some other urls which can upload your files. But I am afraid the administrators may or may nor allow it.

 

[excelsishya]

Hi Venkat,

Why i need new sheet to be added by code itself is,in work place where i intended to use this code , i get workbook with many tabs and in most of cases sheet 3 (summary) will be filled with data which also makes it input.

So i need code which will add sheet and than copy selected tabs data ranges to it,Like Mr Excel's 636 episode" combining worksheet" code, but only 2 variation i need from that code is

1.Range of data to be copied from each tab to new added worksheet should be decided based on my selection of data ranges , when i select tab in group mode and then select specific ranges say A1:A100 before running code.

2 Tab name of each copied tab to be in any of right blank column in combined sheet.

Thanks.

 

[venkat1926]

still I feel in your office computer you have to manually insert a sheet "summary" only once and previous macro with m"sheet3" changed to "summary": will work . However for some reasons you do even this onetime insertion by a modification to my macro here is the modified macro

'I have taken Colo's macro in url "http://www.mrexcel.com/forum/showthread.php?t=3228"
Sub test()
Dim r As Range, j As Integer, k As Integer, dest As Range
'===============
Dim sh As Worksheet, flg As Boolean
For Each sh In Worksheets
If sh.Name Like "summary*" Then flg = True: Exit For
Next
If flg = True Then
GoTo proceed
Else
Sheets.Add.Name = "summary"
End If
'===================
proceed:
j = Worksheets.Count
For k = 1 To j
If Worksheets(k).Name = "summary" Then GoTo nextk
With Worksheets(k)
Set r = Range(.Range("A2"), .Range("A2").End(xlToRight).End(xlDown))
r.Copy
With Worksheets("sheet3")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest.PasteSpecial
Range(dest.End(xlToRight).Offset(0, 1), dest.End(xlDown).End(xlToRight).Offset(0, 1)).FormulaArray = _
    Right(Worksheets(k).Name, 4) & Left(Worksheets(k).Name, 2)
End With
End With
nextk:
Next k
Worksheets(1).Range("A1").EntireRow.Copy Worksheets("sheet3").Range("A1")

End Sub

As I was not able to download your sample file I have not tested.l test it and confirm.

 

[excelsishya]

Hi Venkat,

Thanks for modifying code.I will test this Code this week in office and let you know how it worked.Thanks once again for the help.

 

[excelsishya]

Hi venkat,

When i tested a code i got error at

Range(dest.End(xlToRight).Offset(0, 1), dest.End(xlDown).End(xlToRight).Offset(0, 1)).FormulaArray = _
Right(Worksheets(k).Name, 4) & Left(Worksheets(k).Name, 2)

Data book 3 TABS
tab1 b3 to m317
tab2 c4:m18
tab3 b3:i26


i selected A1 TO A500 in tab 1 Then in Group Mode Selected all Tabs and run code i got error at above point.

Could check if error is due to method of use or in code or may be it is not written to handle above kind of data set mentioned.

Sorry for delay in replying i don't have internet facilities has to wait till weekends to reply.

Thanks