PDA

View Full Version : Pivot Table not updating after Cut and Pasting Values to Data WS



Loss1003
01-10-2018, 11:56 AM
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

paulked
01-11-2018, 10:31 PM
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/article/COUNTA-function-7dc98875-d5c1-46f1-9a82-53f3219e2509

Best regards

Paul Ked