xlPasteFormats Not working in Office 365

[BrotherLouis]

Hi

I have the code below in Office 365. The issue that I have are that it didn't want to copy the format correct, even if I add
the following code.

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False​

If I run this code through Excel 2013 it works perfectly

Sub Acctech_Timesheet()
'
' Acctech_Timesheet Macro
'
'
    Sheets("Acctech Timesheet").Select
    ' ActiveSheet.Buttons.Add(1030.5, 36, 120.75, 33.75).Select
    Sheets("Acctech Timesheet").Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("A2").Select
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
    Selection.Delete
    ' Selection.Cut
    
    ' The code below is to delete the columns to the right of the timesheet: comments etc
    Columns("N:S").Select
    Selection.Delete Shift:=xlToLeft
   
    Range("H19").Select
    Selection.End(xlDown).Select
    Range("I22").Select
    Selection.End(xlDown).Select
    Range("H49").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("H26:J49").Select
    Range("H49").Activate
    Selection.EntireRow.Delete
    Range("C11").Select
   
   
   
    Range("A2").Select
    
    
End Sub

 

[Yongle]

Try this
Works for me with 365

Sub CopySheets()
    Sheets("Acctech Timesheet").Copy After:=Sheets(ThisWorkbook.Worksheets.Count)
    With ActiveSheet.Cells
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    ActiveSheet.Name = "NewName"
End Sub

 

[BrotherLouis]

Hi

I have tried the code it doesn't copy the Format

Thanks
Louis

Try this
Works for me with 365

Sub CopySheets()
    Sheets("Acctech Timesheet").Copy After:=Sheets(ThisWorkbook.Worksheets.Count)
    With ActiveSheet.Cells
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    ActiveSheet.Name = "NewName"
End Sub

 

[Norie]

Louis

You are copying everything from the 'Acctech Timesheet' so you should need to copy the formatting.

How has the formatting been done on that sheet?

 

[Yongle]

The whole sheet is copied including formats first before formulas are overwritten by values
The code works with 365 (I tested it)
- but it is not working for you
- either the workbook has a problem
- or your Excel has a problem

Try creating a totally NEW workbook to test the code
- create the sheet manually (do not copy from the original)

 

[BrotherLouis]

Hi

Some of the format it does copy it over to the new sheet, but where there are cells that are merge it doesn't copy those cells over.

Louis

You are copying everything from the 'Acctech Timesheet' so you should need to copy the formatting.

How has the formatting been done on that sheet?

 

[Yongle]

but where there are cells that are merge it doesn't copy those cells over.

I added Merged Cells to my test worksheet, and used the code I posted in pos#t2 and merged cells are also copied successfully (Office 365)
- the sheet is duplicated in every respect

 

[Yongle]

and now I added Shapes, Active-X objects, tables, pivot tables, charts, comments

everything copies across as expected
- lPasteValues does delete Comments and Pivot Tablessstructures

 

[BrotherLouis]

 

[Yongle]

We believe what you are telling us - images not required

My problem is that I cannot replicate your problem
- perhaps your problem could be caused when 365 is working with old files (created in another version of Excel)

Did you try a totally NEW workbook?
- the reason I am asking is that if does not work in a NEW workbook then try re-installing 365 and then try the same test again in a NEW workbook