I found this macro and it works great and just how I want, but it copy all data (with formulas, and give it me some errors in the new sheet. That's why I need only values.
I also tried to add
but doesn't work. Could somebody please help me
Thanks in advance.
Sub Combine() Dim J As Integer On Error Resume Next Sheets(1).Select Worksheets.Add ' add a sheet in first place Sheets(1).Name = "All" ' copy headings Sheets(2).Activate Range("A1").EntireRow.Select Selection.Copy Destination:=Sheets(1).Range("A1") ' work through sheets For J = 2 To Sheets.Count ' from sheet 2 to last sheet Sheets(J).Activate ' make the sheet active Range("A1").Select Selection.CurrentRegion.Select ' select all cells in this sheets ' select all lines except title Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select ' copy cells selected in the new sheet on last line Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2) Next End Sub