Combining multiple sheets with the use of pastespecial xlpastevalues and xlpasteformats

Question

I've been trying to combine multiple sheets in to one. I have already done it but when I tried to add a line of code

PasteSpecial(xlPasteValues).PasteSpecial(xlPasteFormats)

it leaves my combined sheet blank. Here is the code

On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Consolidated"

Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")

For Each s In ActiveWorkbook.Sheets
    If s.Name <> "Consolidated" And s.Name <> "Report" And s.Name <> 
       "Table" And s.Name <> "Values" And s.Name <> "Button" And s.Name <> 
       "PivotTable" And s.Name <> "Task" And s.Name <> "Mapping" 
        Then
        Application.GoTo Sheets(s.Name).[a1]
        Selection.CurrentRegion.Select
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
        Selection.Copy Destination:=Sheets("Consolidated"). _
        Cells(Rows.Count, 1).End(xlUp)(3) _
        .PasteSpecial(xlPasteValues).PasteSpecial(xlPasteFormats)  '<<-- The problem. It does not give me error but it only give me a blank cell everytime I add that line of code.

    End If
Next

*.PasteSpecial(xlPasteValues).PasteSpecial(xlPasteFormats)* `PasteSpecial` returns `Boolean` value. You true to use `PasteSpecial` on this type, but it doesn't have one - that's why you get the error. — JohnyL, Aug 24 '18 at 07:15
Hi @JohnyL is there anything I can do so it won't return as boolean — Angelo, Aug 24 '18 at 07:20
Using `On Error Resume Next` hides all error messages, but the errors still occur, you just cannot see them. It's like closing your eyes which also doen't remove the error. Hiding errors don't fix them. Remove that line and see which errors you get and then fix them! • You cannot debug/fix code when using `On Error Resume Next` like you did. • I also recommend to read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Aug 24 '18 at 07:25
It says unable to get the pastespecial property of the range class. how to fix that? — Angelo, Aug 24 '18 at 07:28

Durgaprasad · Accepted Answer · 2018-08-24T08:40:57.367

2

Use Like this

Selection.Copy

Sheets("Consolidated").Cells(Rows.Count, 1).End(xlUp)(3).PasteSpecial (xlPasteFormats)

Sheets("Consolidated").Cells(Rows.Count, 1).End(xlUp)(3).PasteSpecial (xlPasteValues)

edited Aug 24 '18 at 08:40

answered Aug 24 '18 at 08:03

Durgaprasad

323
2
14

I have a formula on some of the cell that is why I want to use paste special. – Angelo Aug 24 '18 at 08:31
I just used like this With Sheets("Consolidated").Cells(Rows.Count, 1).End(xlUp)(3) .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _ , SkipBlanks:=False, Transpose:=False .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With – Angelo Aug 24 '18 at 09:20

Combining multiple sheets with the use of pastespecial xlpastevalues and xlpasteformats

1 Answers1