Consulting

Results 1 to 2 of 2

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

  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

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Hi there

    This is a shortened version of your code:

    Sub wstodata3()
        Dim x As Long
        Application.ScreenUpdating = False
        Sheet5.Range("WstoData1").Copy
        Sheet3.Range("A2").PasteSpecial Paste:=xlPasteValues
        For x = 2 To 14
            Sheet5.Range("WstoData" & x).Copy
            Sheet3.Cells(2, x + 3).PasteSpecial Paste:=xlPasteValues
        Next
    End Sub
    And even shorter as the formatting is the same:

    Sub wstodata4()
        Dim x As Long
        Application.ScreenUpdating = False
        Sheet5.Range("WstoData1").Copy Sheet3.Range("A2")
        For x = 2 To 14
            Sheet5.Range("WstoData" & x).Copy Sheet3.Cells(2, x + 3)
        Next
    End Sub
    (Sum of Amount) and (Max of Filing Date) are defaulted to 0 to 01/0/1990
    There are errors in your data.

    Also, when no values are located in the range the code run-time error '1004'


    You are using COUNTA in the range definition. If there is nothing there it counts nothing and errors, you can't copy nothing!

    See here https://support.office.com/en-us/art...2-53f3219e2509

    Best regards

    Paul Ked
    Last edited by paulked; 01-11-2018 at 10:49 PM.
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

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