Consulting

Results 1 to 2 of 2

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

  1. #1

    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 
    
    
    Formatting tags added by mark007
    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 
    
    
    Formatting tags added by mark007
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  2. #2
    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 
    
    
    Formatting tags added by mark007
    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 
    
    
    Formatting tags added by mark007
    (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
  •