Prashant, Thanks for sharing this, i must say its an amasing post......
Harry On Jun 5, 10:53 pm, Prashant Pednekar <prashant...@gmail.com> wrote: > Hi all > > received the macro on below link..giving this for your referance. > > http://www.mrexcel.com/archive/VBA/17846.html > > Posted by Damon Ostrander on January 25, 2002 3:03 PM > > Hi SLP, > Here's a little macro that makes merging sheets easy. Just select the > sheets you want merged making sure the active sheet (visible one) is > the one you want to merge to. The rows from all the other selected > sheets will be copied to end of the active sheet in left-to-right > order of worksheet tabs. It allows you to declare a number of header > lines (NHR value) so that you don't copy the worksheet's headers to > the first sheet. > > This macro should be put into a macro module. If you don't know how to > install/run a macro, just follow up and I'll give you instructions. > > Damon > > Sub MergeSheets() > > ' Merges data from all the selected worksheets onto the end of the > ' active worksheet. > > Const NHR = 1 'Number of header rows to not copy from each MWS > > Dim MWS As Worksheet 'Worksheet to be merged > Dim AWS As Worksheet 'Worksheet to which the data are transferred > Dim FAR As Long 'First available row on AWS > Dim LR As Long 'Last row on the MWS sheets > > Set AWS = ActiveSheet > > For Each MWS In ActiveWindow.SelectedSheets > If Not MWS Is AWS Then > FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1 > LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row > MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR) > End If > Next MWS > > End Sub > > On Sat, Jun 5, 2010 at 10:57 PM, Prashant Pednekar > > > > <prashant...@gmail.com> wrote: > > Dear Experts. > > > I have a eight workbooks of telephone billing statement which are > > converted from pdf file to excel. Each workbook contain 135 > > worksheets. Each sheet contain the data like Date/Time, Number, > > Destination, Duration, Call charges. in two table. The useful data of > > each worksheet is from Cell A14 to Cell M74. > > > My requirement is merge data from all worksheets on worksheet > > ascendingly i.e from sheet 1 data to sheet 135. I am confidant that > > this can be solved using macro. I request your help to guide me for > > the logic. > > > Regards. > > > Prashant.- Hide quoted text - > > - Show quoted text - -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
