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
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