Consulting

Results 1 to 2 of 2

Thread: Pivot Table not updating after Cut and Pasting Values to Data WS

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    May 2009
    Posts
    76
    Location

    Pivot Table not updating after Cut and Pasting Values to Data WS

    I can't figure out the problem with my Pv table after I've updated the data by cut and pasting (values only) from another WS into the dynamic table range via Marco Vba, the table does not update correctly.

    The Dates and Dollar amounts columns that are carried over seem to be corrupting the data. therefore after I've run the code the columns (Sum of Amount) and (Max of Filing Date) are defaulted to 0 to 01/0/1990
    Also, when no values are located in the range the code run-time error '1004' - the clipboard cannot be emptied. Another program might be using the clipboard:

    It works when i manually c & p but not when I try and cut and paste by vba macro.

    using excel 2010

    See attached spreadsheet.

     wstodata1()
    'Copy to Pivot Table Data located on "Data WS"
    'long verison bc when no values are in a column the code will still run
    
     Application.ScreenUpdating = False
     Sheet5.Activate
            Range("WstoData1").Select
            Selection.Copy
    Sheet3.Activate
            Range("A2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
          
    'civil dismissal
    Application.ScreenUpdating = False
     Sheet5.Activate
            Range("WstoData2").Select
            Selection.Copy
    Sheet3.Activate
            Range("E2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
     
     'civil new filing
     Application.ScreenUpdating = False
     Sheet5.Activate
            Range("WstoData3").Select
            Selection.Copy
    Sheet3.Activate
            Range("F2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
    
    'civil suit
    Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData4").Select
            Selection.Copy
    Sheet3.Activate
            Range("G2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
    
    'judgement
      Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData5").Select
            Selection.Copy
    Sheet3.Activate
            Range("H2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
    
    'Chapter 7
      Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData6").Select
            Selection.Copy
    Sheet3.Activate
            Range("I2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
        
    'federal tax lien
      Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData7").Select
            Selection.Copy
    Sheet3.Activate
            Range("J2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
    'City tax lien
      Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData8").Select
            Selection.Copy
    Sheet3.Activate
            Range("K2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
    'State tax lien
      Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData9").Select
            Selection.Copy
    Sheet3.Activate
            Range("L2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
    'State tax Warrant
      Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData10").Select
            Selection.Copy
    Sheet3.Activate
            Range("M2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
    'Creditor
      Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData11").Select
            Selection.Copy
    Sheet3.Activate
            Range("N2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
    
    'Filing Date
      Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData12").Select
            Selection.Copy
    Sheet3.Activate
            Range("O2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
    'Amount
    Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData13").Select
            Selection.Copy
    Sheet3.Activate
            Range("P2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
    'State
      Application.ScreenUpdating = False
    Sheet5.Activate
            Range("WstoData14").Select
            Selection.Copy
    Sheet3.Activate
            Range("Q2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
          Application.ScreenUpdating = True
             
    End Sub
    End Sub
    shorter verison

    Sub wstodata2()
    'Copy to Pivot Table Data located on "Data WS"
    'short verison
    
     Application.ScreenUpdating = False
     Sheet5.Activate
             Range("WstoData1, Wstodata2, Wstodata3, Wstodata4, Wstodata5,  Wstodata6, Wstodata7, Wstodata8, Wstodata9, Wstodata10, Wstodata11,  Wstodata12, Wstodata13, Wstodata14").Select
            'wstodata12 and 13, and 14 cuasing error (error when no data in 13)
            Selection.Copy
    Sheet3.Activate
            Range("B2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
         
          Application.ScreenUpdating = True
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •