Consolidate Worksheets - Help pasting values

[exc4libur]

Dear experts,

I am having trouble tweaking the code below. I need to paste values and formats to a new workbook (so the one i'm copying from stays intact).

Sub CombineWorksheets1()
Dim Ws As Worksheet
Application.ScreenUpdating = False
Sheets(1).Select
Worksheets.Add
With Sheets(1)
    .UsedRange.Offset(1).Clear
For Each Ws In Worksheets
  If .Index <> Ws.Index Then Ws.UsedRange.Offset(1).Copy .Cells(.Rows.Count, 1).End(xlUp)(2)
  Application.CutCopyMode = False
Next
    Application.GoTo .Cells(1), True
End With
    Application.ScreenUpdating = True
End Sub

Ty

[jindon]

Try change to

            If .Index <> Ws.Index Then
                Ws.UsedRange.Offset(1).Copy
                .Cells(.Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteFormats
                .Cells(.Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
                Application.CutCopyMode = False
            End If

[exc4libur]

Hi Jindon!
Thank you for replying. I tested it, but i got an error due to merged cells.
Also, to be safe, instead of pasting to a new sheet how do paste it to a new workbook?
Ty.

[jindon]

Then how about

Sub CombineWorksheets1()
    Dim Ws As Worksheet
    Application.ScreenUpdating = False
    With Workbooks.Add.Sheets(1)
        For Each Ws In ThisWorkbook.Worksheets
            Ws.UsedRange.Offset(1).Copy .Cells(.Rows.Count, 1).End(xlUp)(2)
            .UsedRange.Value = .UsedRange.Value
            Application.CutCopyMode = False
        Next
        Application.GoTo .Cells(1), True
    End With
    Application.ScreenUpdating = True
End Sub

[exc4libur]

Then how about

Sub CombineWorksheets1()
    Dim Ws As Worksheet
    Application.ScreenUpdating = False
    With Workbooks.Add.Sheets(1)
        For Each Ws In ThisWorkbook.Worksheets
            Ws.UsedRange.Offset(1).Copy .Cells(.Rows.Count, 1).End(xlUp)(2)
            .UsedRange.Value = .UsedRange.Value
            Application.CutCopyMode = False
        Next
        Application.GoTo .Cells(1), True
    End With
    Application.ScreenUpdating = True
End Sub

I get an error 6 'overflow' problem, highlighting .usedrange line.

[jindon]

What happen if move that line to outside loop.
i.e

        For Each Ws In ThisWorkbook.Worksheets
            Ws.UsedRange.Offset(1).Copy .Cells(.Rows.Count, 1).End(xlUp)(2)
            Application.CutCopyMode = False
        Next
       .UsedRange.Value = .UsedRange.Value

If you still have problem, you need to upload a workbook.

[exc4libur]

It works for some wkbooks and gives an error for others.
Anyways, I gets the job done. Thank you so very much