Copy and paste Command button

Question

Can someone help me with a Macro I've been on for a bit where I've got a command button to copy and paste a replica of my page underneath my current work on the same sheet.

I've got most of the VBA working but having a little trouble with pasting the full content?

content meaning formulas, cell colors, and fonts etc.

Answer 1

OK

Then try the code

'''**************************************************************************

Private Sub CommandButton1_Click()

Dim xSheet As Worksheet

Application.ScreenUpdating = False

Set xSheet = ActiveSheet

If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then

        xSheet.Range("A1:O83 ").Copy

        With xSheet.Range("A86")

                .PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

                .PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

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

                Application.CutCopyMode = False

        End With

End If

Application.ScreenUpdating = True

End Sub

'''''******************************************************************************

Answer 2

so at the moment I have something very similar to this?

Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False

    Dim xSheet As Worksheet

    Set xSheet = ActiveSheet

        If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then

            xSheet.Range("A1:O83 ").Copy

            xSheet.Range("A86").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

        End If

    Application.ScreenUpdating = True

End Sub

Answer 3

OK

If I understood correctly

This code will paste 

a) Values only (NO Formulas)

b) Cell Format (Font, color, etc)

c) Conditional Formating

d) Data Validation (Drop-down lists)

Note:

The code has been tested and works fine

''' ***********************************************************************

Sub copyData()

Range("A1:O83").Copy

With Range("A86")

    .PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    .PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

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

    Application.CutCopyMode = False

End With

End Sub

'''''''**************************************************************************

As for the rest of your questions, It is hard to guess what going on in your workbook without any details of your scenario??!!!!???

Answer 4

I really don't think I'm that far off getting it I have just found if I try to cut and paste without the command button it seems my paste specials are not working?

or maybe even missing.

Could this be it? and why are they missing and how do I get them back?

Step:1 Hit command button 

Outcome:1 pastes cells A1:O83 into A86:O169 but no auto sums, dropdown lists or fonts/cell colors are copied?

Answer 5

Hi

It is difficult to visualize your requirement with the current information, your workbook is too complex to give you a solution without having access to your file.

Kindly suggest prepare and upload a sample file to Onedrive, Dropbox, etc ... and share the link here

Please, 

a)  Remove any personal/sensitive data

b) Keep the headers, table structure, and formulas as they actually are

c) ZIP the file if contain macros, shapes, or other objects.

d) Provide more details of your scenario and goals. Also, post the results expected.

It will help us to give you a prompt and right solution.

If you need help with how to upload the file please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

You may also try

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Regards

Jeovany