View Single Post
 
Old 10-31-2011, 12:39 AM
Catalin.B Catalin.B is offline Windows Vista Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

You can try this macro: (first select all worksheets to be merged with ctrl+sheet tab)
Code:
Sub MergeSheets()

'  Appends 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 (appended)
   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
Reply With Quote