none
Combine all sheets to one sheet, paste special values only? RRS feed

  • Question

  • Help,My paste is not working? 

    Dim ws As Worksheet
        
        Application.ScreenUpdating = False
        Sheets("Summary").Activate
        
        For Each ws In Worksheets
            If ws.Name <> "Summary" Then
                ws.Range("A1:H91").Copy
                ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
               .PasteSpecial Paste:=xlPasteValues


    heads up

    Tuesday, February 4, 2014 3:16 AM

All replies

  • Try this - note the space and _ after Offset(1, 0)

        Dim ws As Worksheet
        Application.ScreenUpdating = False
        Sheets("Summary").Activate
        For Each ws In Worksheets
            If ws.Name <> "Summary" Then
                ws.Range("A1:H91").Copy
                Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0) _
                    .PasteSpecial Paste:=xlPasteValues
            End If
        Next ws


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, February 4, 2014 6:31 AM
  • Hans,

    Whats the trick to add other "Pastes"?       

    .PasteSpecial Paste:=xlPasteFormats
     .PasteSpecial Paste:=xlPasteColumnWidths
       .PasteSpecial Paste:=xlPasteValues


    heads up

    Wednesday, February 5, 2014 1:56 AM
  • Change

                Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0) _
                    .PasteSpecial Paste:=xlPasteValues

    to

                With Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)
                    .PasteSpecial Paste:=xlPasteValues
                    .PasteSpecial Paste:=xlPasteFormats
                    .PasteSpecial Paste:=xlPasteColumnWidths
                End With


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Wednesday, February 5, 2014 6:38 AM
  • om gosh! Thanks

    heads up

    Wednesday, February 5, 2014 2:43 PM
  • Han,s

    I wanted to skip adding one page, some how its not working? your thoughts.    

    If ws.Name <> "and this sheet" and _

       ws.Name <> "Summary" Then


    heads up

    Friday, February 21, 2014 2:38 PM
  • Make sure that there is no blank line in between those two lines.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, February 21, 2014 5:58 PM