Problem in macro for special paste

[RyanInt]

I have a problem with a macro that performs a Special Paste (values without copying formulas) to a new workbook after clicking a button and record the new workbook.

Here is the code used:

Public Sub CommandButton1_Click()
sort = "Sort" & ".xlsx"
ActiveSheet.Select
Range("A1:B7" ).Select
Selection.Copy
Workbooks.Add.SaveAs sort
Worksheets.Add.Name = "Consumption"
Workbooks(sort).Activate
Sheets("Consumption" ).Select
Range("A1" ).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Workbooks(sort).Save
Workbooks(sort).Close
End Sub

[!const]

It is "simple enough" in fact. Clear your Excel Copy after another action. Whether it's in code, or "normal" utilization.

Besides, you should have seen that your selection was not as small dots flashing on a copy.

You have copied and pasted almost to the following.

• You created your new file.
• You can chat live with your first file.
• You copy your selection.
• You can chat live with your new file.
• You paste.
• Enjoy

Public Sub CommandButton1_Click()
    sort = "Sort" & ".xls"
    originalsheet = ActiveSheet.Name
    originalclass = ActiveWorkbook.Name
    Workbooks.Add.SaveAs sort
    Worksheets.Add.Name = "Consumption"

    Workbooks(originalclass).Activate
    Sheets(originalsheet).Select
    Range("A1:B7" ).Select
    Selection.Copy
   
    Workbooks(sort).Activate
    Sheets("Consumption" ).Select
    Range("A1" ).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Workbooks(sort).Save
    Workbooks(sort).Close
End Sub

[RyanInt]

Thank you very much,

I still had a persistent error but removing the Range("A1" ).Select paste special before it works.

Now I have another concern, I wish it performs as a special paste when I paste values and number formats. In the current macro it only values, so I get to do only format. But the 2, I can not find?

[!const]

We must do both as a result no? A sudden you glue the values and formats.

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

Or maybe I misunderstood the question .