Macro stops working when re-opened

**BarryDragon** · 08-29-2011, 11:39 AM

I have written a basic code to paste and sort data. It works fine when i write it, though if I close Excel it never works again. Can anyone see why.

Code is designed to paste data copied from a report sorted to be used for a mail merge.

Sub Update_Paypal()

Application.ScreenUpdating = False

'clear Data from Sheets

    Sheets("PayPal Input").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents
    
    Sheets("SortedData").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents

'paste data to be sorted
    Sheets("PayPal Input").Select
    Range("A1").Select
    ActiveSheet.Paste
    
'sort data
        Columns("P:P").Select
    Range("A1:AP10").Sort Key1:=Range("P1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    
    Sheets("SortedData").Select
    Range("A1").Select
    ActiveSheet.Paste
    
    
    Sheets("Admin").Select

    
Application.ScreenUpdating = True

    ActiveWorkbook.Save
End Sub

**Leith Ross** · 08-29-2011, 12:10 PM

Hello BarryDragon,

Welcome to the Forum!

How is the macro run: manually or automatically?
If automatically then which event calls it?

**BarryDragon** · 08-29-2011, 12:34 PM

Hi, the Macro is run by a button click, I have the data copied already

**Leith Ross** · 08-29-2011, 12:37 PM

Hello Barry,

When you click the button, the macro runs then nothing happens on the sheet or does it error?

If you have run the macro on the data then saved it then you wouldn't see anything happen when the macro is run again on the saved data.

**BarryDragon** · 08-30-2011, 05:58 AM

the macro produces an error on the first paste function. Seems that the data in the clipboard might be disappearing. Though I do not have a clue why, as i said it works if i make a new spreadsheet and put the code in, and I have another spreadsheet that does something similar (by pasting in data already in clipboard) and works fine.

**snb** · 08-30-2011, 06:52 AM

Where do those to be copied data come from ?

Sub Update_Paypal()
  Application.ScreenUpdating = False

   Sheets("SortedData").usedrange.ClearContents

   with Sheets("PayPal Input")
     .usedrange.clearcontents
     .Paste
     .Range("A1:AP10").Sort .Range("P1")

     with .usedrange    
       Sheets("SortedData").cells(1).resize(rows.count,columns.count)=.value
    end with
  end with

  Application.ScreenUpdating = True
End Sub

**BarryDragon** · 08-30-2011, 08:35 AM

The data is copied from a paypal report in excel.

When I use our code I get an error on the paste function...

**snb** · 08-30-2011, 09:01 AM

if the workbook 'paypaldata' contains those data

Sub Update_Paypal()
  Application.ScreenUpdating = False

  sn = workbooks("Paypaldata").sheets(1).usedrange
  Sheets("SortedData").usedrange.ClearContents

   with Sheets("PayPal Input")
     .usedrange.clearcontents
     .cells(1).resize(ubound(sn),ubound(sn,2))=sn
     .Range("A1:AP10").Sort .Range("P1")

     with .usedrange    
       Sheets("SortedData").cells(1).resize(.rows.count,.columns.count)=.value
    end with
  end with

  Application.ScreenUpdating = True
End Sub