Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 06-11-2009
Member
 
Join Date: Jun 2009
Posts: 3,960
Problem in macro for special paste

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:

Code:
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
Reply With Quote
  #2  
Old 06-11-2009
Member
 
Join Date: Nov 2008
Posts: 1,054
Re: Problem in macro for special paste

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

Code:
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
Reply With Quote
  #3  
Old 06-11-2009
Member
 
Join Date: Jun 2009
Posts: 3,960
Re: Problem in macro for special paste

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?
Reply With Quote
  #4  
Old 06-11-2009
Member
 
Join Date: Nov 2008
Posts: 1,054
Re: Problem in macro for special paste

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

Code:
'
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 .
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Problem in macro for special paste"
Thread Thread Starter Forum Replies Last Post
I cannot do Copy and Paste or Paste Special between Excel Workbooks Acca-OR Windows Software 5 26-04-2012 04:16 AM
Paste Special function does not work when I copy formulas in Excel Tarani MS Office Support 2 24-02-2012 07:22 PM
Want to page negative time via paste special in Excel Ojsuta MS Office Support 4 24-02-2012 01:52 PM
Macro onClick copy/paste Excel MACE Software Development 3 09-12-2009 03:58 PM
Macro sum problem in excel 2007 Radley Windows Software 2 07-04-2009 09:46 PM


All times are GMT +5.5. The time now is 03:06 PM.