Multiple excel sheets in merge in single excel sheet

Multiple excel sheets in merge in single excel sheet

Post by Kishor » Sat, 02 Jun 2012 16:45:22



Hi Excel Gurus,

I have three different excel sheets with data & top of the first at rows
names are described, all though three excel sheets headings at rows are
little vary , I would like to merge all these 3 excel sheets in single
workbook excel sheet.

Please help& do the needful.

 File attached.

Regards,

Kishore K.

  consolidated location asset sheet.xlsx
12K Download

  Test file1.xlsx
69K Download

  Test file2.xlsx
22K Download

  Test file3.xlsx
16K Download
 
 
 

Multiple excel sheets in merge in single excel sheet

Post by Kishor » Sat, 02 Jun 2012 18:08:25


Hi , Pls help....


> Hi Excel Gurus,

> I have three different excel sheets with data & top of the first at rows
> names are described, all though three excel sheets headings at rows are
> little vary , I would like to merge all these 3 excel sheets in single
> workbook excel sheet.

> Please help& do the needful.

>  File attached.

> Regards,

> Kishore K.


 
 
 

Multiple excel sheets in merge in single excel sheet

Post by Mahesh para » Mon, 04 Jun 2012 05:36:32


Hi Kishore

Try :
Change worksheet name as per your requirement

Sub Mtest()
Dim i As Integer
Dim ws As Worksheet
Worksheets(Worksheets.Count).Activate
For i = 1 To Worksheets.Count
'Change sheet name where you want to copy data from all other sheets
If Sheets(i).Name <> "Mainsheet" Then
'change you range here
Sheets(i).Range("A1:A25").Copy Destination:=Sheets("Mainsheet").Range("A" &
Rows.Count).End(xlUp).Offset(1)
'OR if header of all sheets are common  & copy used rows
'Sheets(i).UsedRange.Offset(1).Resize(ActiveSheet.UsedRange.Rows.Count -
1).Copy Destination:=Sheets("Mainsheet").Range("A" &
Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

HTH
Mahesh


> Hi , Pls help....


>> Hi Excel Gurus,

>> I have three different excel sheets with data & top of the first at rows
>> names are described, all though three excel sheets headings at rows are
>> little vary , I would like to merge all these 3 excel sheets in single
>> workbook excel sheet.

>> Please help& do the needful.

>>  File attached.

>> Regards,

>> Kishore K.

>>  --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


 
 
 

Multiple excel sheets in merge in single excel sheet

Post by Rajan_Verm » Mon, 04 Jun 2012 16:46:28


Use the following Code :

Sub Compile()

On Error GoTo Err_Clear:

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Dim Fso As New Scripting.FileSystemObject

Dim Path As String

Application.FileDialog(msoFileDialogFolderPicker).Title = "Select Folder to
Pick Downloaded Bills"

Application.FileDialog(msoFileDialogFolderPicker).Show

Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) &
"\"

If Path = "" Then Exit Sub

Application.FileDialog(msoFileDialogFolderPicker).Title = "Select Folder to
Save Compiled File"

CompilePath = Application.FileDialog(msoFileDialogFolderPicker).Show

compiledPath =
Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"

If compiledPath = "" Then Exit Sub

Dim Counter

Dim File As File

Dim FOlder As FOlder

Dim wb As Workbook

Dim ws As Worksheet

Dim AcWb As Workbook

Set AcWb = ActiveWorkbook

             ActiveWorkbook.Sheets.Add

             ActiveSheet.Name = "Index"

Set FOlder = Fso.GetFolder(Path)

        For Each File In FOlder.Files

             Counter = Counter + 1

             Set wb = Workbooks.Open(Path & File.Name)

                            If Application.Ready = True Then

                                 wb.Sheets("Index").Activate

                                 ActiveSheet.UsedRange.Copy

                                  AcWb.Sheets("Index").Activate

                                  Range("A1000000").End(xlUp).Select

                                  ActiveSheet.Paste

                                 Application.CutCopyMode = False

                                 wb.Close

                            End If

        Next

If Counter > 0 Then

AcWb.SaveAs compiledPath & "Compiled", xlExcel12

AcWb.Close

End If

Err_Clear:

Err.Clear

Resume Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

        If Counter < 1 Then

        MsgBox "No File Found For Compile", vbInformation

        Else

        MsgBox Counter & " File Has been Compiled, Please Find your File at"
& vbCrLf & compiledPath, vbInformation

        End If

End Sub

Read More on :

http://excelpoweruser.blogspot.in/

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of Kishore
Sent: 01 June 2012 1:15

Subject: $$Excel-Macros$$ Multiple excel sheets in merge in single excel
sheet

Hi Excel Gurus,

I have three different excel sheets with data & top of the first at rows
names are described, all though three excel sheets headings at rows are
little vary , I would like to merge all these 3 excel sheets in single
workbook excel sheet.

Please help& do the needful.

 File attached.

Regards,

Kishore K.

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

 
 
 

Multiple excel sheets in merge in single excel sheet

Post by dguillett » Wed, 06 Jun 2012 22:27:25


You can use the vba DIR to open each file in the current folder and then copy the used range.offset(1) to the next available row in the consolidate file. If necessary, you can specify the files within an array

Don Guillett
Microsoft MVP Excel
SalesAid Software

From: Kishore
Sent: Friday, June 01, 2012 2:45 AM

Subject: $$Excel-Macros$$ Multiple excel sheets in merge in single excel sheet

Hi Excel Gurus,

I have three different excel sheets with data & top of the first at rows names are described, all though three excel sheets headings at rows are little vary , I would like to merge all these 3 excel sheets in single workbook excel sheet.

Please help& do the needful.

File attached.

Regards,

Kishore K.

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------

 
 
 

1. Distributing cell values from one Excel sheet to another Excel sheet on another computer - HOW TO

hi!

Seriously, I have one machine running excel xp w2000 (i could make it excel
2000 on nt or xp if necessary) - let's call this the central machine

i have 3 other machines running excel on nt or 2000 platforms - let's call
these the perpherial machines

i only need one way updating (when a cell is changes) from the central
machine to the other machines - about 70,000 cells on the central machine
need to be updated - most at any one time is about a few hundred to 3000 - i
do not expect them to be updated instantly, but i need them updated without
anyone saving on the central machine or updating links on the other
machine - i need them updated when the cell changes - the spreadsheets on
all machines have the same layout - no error checking needed

maybe you do not know exactly how to do this - maybe you know just a part of
how to do this

thank you for your help

2. C64 CD

3. Excel: multiple sheet printing with diff. footer for each sheet?

4. Can't record audio with SONY CRX100E and CD Right v1.50

5. Need to copy one excel sheet to another excel sheet

6. Additional Exchange Server

7. mail merge and Excel -- Multiple sheets possible?

8. Wangtek 500MB SCSI Streamer for SUN-Classic ?

9. Excel: can add-ins be saved within an Excel file?

10. Using MS Excel 4 add-ins with Excel 5.0a...

11. calculate from multiple sheets & calculate from multiple sheets with conditions

12. Merging 2 Excel sheet and adding the numbers

13. Maill Merge in Two Excel Sheet