Compile Error: Object Required VBA help

T

tmsmyer

New Member
Joined
Sep 17, 2015
Messages
38
I'm having an issue with an "object required" error with my macro. I have 14 different pivot tables that are being changed (the filter is being changed). When the error appears, the highlighted part is "Set NewDate14 =" in the last line of the macro part supplied below.

Anyone have any idea on a solution? If you need more info, let me know

Code: 
Dim PT1, PT2, PT3, PT4, PT5, PT6, PT7, PT8, PT9, PT10, PT11, PT12, PT13, PT14 As PivotTable
Dim Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12, Field13, Field14 As PivotField
Dim NewDate1, NewDate2, NewDate3, NewDate4, NewDate5, NewDate6, NewDate7, NewDate8, NewDate9, NewDate10, NewDate11, NewDate12, NewDate13, NewDate14 As String


Set PT1 = Worksheets("netting").PivotTables("Sunday-Day")
Set PT2 = Worksheets("netting").PivotTables("Sunday-Night")
Set PT3 = Worksheets("netting").PivotTables("Monday-Day")
Set PT4 = Worksheets("netting").PivotTables("Monday-Night")
Set PT5 = Worksheets("netting").PivotTables("Tuesday-Day")
Set PT6 = Worksheets("netting").PivotTables("Tuesday-Night")
Set PT7 = Worksheets("netting").PivotTables("Wednesday-Day")
Set PT8 = Worksheets("netting").PivotTables("Wednesday-Night")
Set PT9 = Worksheets("netting").PivotTables("Thursday-Day")
Set PT10 = Worksheets("netting").PivotTables("Thursday-Night")
Set PT12 = Worksheets("netting").PivotTables("Friday-Day")
Set PT12 = Worksheets("netting").PivotTables("Friday-Night")
Set PT13 = Worksheets("netting").PivotTables("Saturday-Day")
Set PT14 = Worksheets("netting").PivotTables("Saturday-Night")


Set Field1 = PT1.PivotFields("Adjustment Date")
Set Field2 = PT2.PivotFields("Adjustment Date")
Set Field3 = PT3.PivotFields("Adjustment Date")
Set Field4 = PT4.PivotFields("Adjustment Date")
Set Field5 = PT5.PivotFields("Adjustment Date")
Set Field6 = PT6.PivotFields("Adjustment Date")
Set Field7 = PT7.PivotFields("Adjustment Date")
Set Field8 = PT8.PivotFields("Adjustment Date")
Set Field9 = PT9.PivotFields("Adjustment Date")
Set Field10 = PT10.PivotFields("Adjustment Date")
Set Field11 = PT11.PivotFields("Adjustment Date")
Set Field12 = PT12.PivotFields("Adjustment Date")
Set Field13 = PT13.PivotFields("Adjustment Date")
Set Field14 = PT14.PivotFields("Adjustment Date")


Set NewDate1 = Worksheets("netting").Range("B1").Value
Set NewDate2 = Worksheets("netting").Range("F1").Value
Set NewDate3 = Worksheets("netting").Range("J1").Value
Set NewDate4 = Worksheets("netting").Range("N1").Value
Set NewDate5 = Worksheets("netting").Range("R1").Value
Set NewDate6 = Worksheets("netting").Range("V1").Value
Set NewDate7 = Worksheets("netting").Range("Z1").Value
Set NewDate8 = Worksheets("netting").Range("AD1").Value
Set NewDate9 = Worksheets("netting").Range("AH1").Value
Set NewDate10 = Worksheets("netting").Range("AL1").Value
Set NewDate11 = Worksheets("netting").Range("AP1").Value
Set NewDate12 = Worksheets("netting").Range("AT1").Value
Set NewDate13 = Worksheets("netting").Range("AX1").Value
Set NewDate14 = Worksheets("netting").Range("BB1").Value
 
Sort by date Sort by votes
In VBA you need to declare each variable individually

Code: 
Dim NewDate1 [COLOR=#ff0000][B]As String[/B][/COLOR], NewDate2 [COLOR=#ff0000][B]As String[/B][/COLOR], .... , NewDate14 [COLOR=#FF0000][B]As String[/B][/COLOR]

rather than 

Dim NewDate1, NewDate2, .... , NewDate14 [COLOR=#ff0000][B]As String[/B][/COLOR]

At the moment, NewDate1, NewDate2 etc will be the default Variant variable type, which is why the compiler only has a problem with the string variable NewDate14.

Then you need to get rid of the Set in the following lines:

Code: 
[COLOR=#FF0000][B]Set[/B][/COLOR] NewDate1 = Worksheets("netting").Range("B1").Value
[COLOR=#FF0000][B]Set[/B][/COLOR] NewDate2 = Worksheets("netting").Range("F1").Value
.....
[COLOR=#FF0000][B]Set[/B][/COLOR] NewDate14 = Worksheets("netting").Range("BB1").Value
 
Upvote 0
Ok I did as you said and it solved that issue, but now I get a runtime error '91': Object variable or With block variable not set. It highlights the portion of the macro that says "Set Field11 = PT11.PivotFields("Adjustment Date")"

Code: 
Dim PT1 As PivotTable
Dim PT2 As PivotTable
Dim PT3 As PivotTable
Dim PT4 As PivotTable
Dim PT5 As PivotTable
Dim PT6 As PivotTable
Dim PT7 As PivotTable
Dim PT8 As PivotTable
Dim PT9 As PivotTable
Dim PT10 As PivotTable
Dim PT11 As PivotTable
Dim PT12 As PivotTable
Dim PT13 As PivotTable
Dim PT14 As PivotTable


Dim Field1 As PivotField
Dim Field2 As PivotField
Dim Field3 As PivotField
Dim Field4 As PivotField
Dim Field5 As PivotField
Dim Field6 As PivotField
Dim Field7 As PivotField
Dim Field8 As PivotField
Dim Field9 As PivotField
Dim Field10 As PivotField
Dim Field11 As PivotField
Dim Field12 As PivotField
Dim Field13 As PivotField
Dim Field14 As PivotField


Dim NewDate1 As String
Dim NewDate2 As String
Dim NewDate3 As String
Dim NewDate4 As String
Dim NewDate5 As String
Dim NewDate6 As String
Dim NewDate7 As String
Dim NewDate8 As String
Dim NewDate9 As String
Dim NewDate10 As String
Dim NewDate11 As String
Dim NewDate12 As String
Dim NewDate13 As String
Dim NewDate14 As String


Set PT1 = Worksheets("netting asins").PivotTables("Sunday-Day")
Set PT2 = Worksheets("netting asins").PivotTables("Sunday-Night")
Set PT3 = Worksheets("netting asins").PivotTables("Monday-Day")
Set PT4 = Worksheets("netting asins").PivotTables("Monday-Night")
Set PT5 = Worksheets("netting asins").PivotTables("Tuesday-Day")
Set PT6 = Worksheets("netting asins").PivotTables("Tuesday-Night")
Set PT7 = Worksheets("netting asins").PivotTables("Wednesday-Day")
Set PT8 = Worksheets("netting asins").PivotTables("Wednesday-Night")
Set PT9 = Worksheets("netting asins").PivotTables("Thursday-Day")
Set PT10 = Worksheets("netting asins").PivotTables("Thursday-Night")
Set PT12 = Worksheets("netting asins").PivotTables("Friday-Day")
Set PT12 = Worksheets("netting asins").PivotTables("Friday-Night")
Set PT13 = Worksheets("netting asins").PivotTables("Saturday-Day")
Set PT14 = Worksheets("netting asins").PivotTables("Saturday-Night")


Set Field1 = PT1.PivotFields("Adjustment Date")
Set Field2 = PT2.PivotFields("Adjustment Date")
Set Field3 = PT3.PivotFields("Adjustment Date")
Set Field4 = PT4.PivotFields("Adjustment Date")
Set Field5 = PT5.PivotFields("Adjustment Date")
Set Field6 = PT6.PivotFields("Adjustment Date")
Set Field7 = PT7.PivotFields("Adjustment Date")
Set Field8 = PT8.PivotFields("Adjustment Date")
Set Field9 = PT9.PivotFields("Adjustment Date")
Set Field10 = PT10.PivotFields("Adjustment Date")
Set Field11 = PT11.PivotFields("Adjustment Date")          <-----Highlighted Line
Set Field12 = PT12.PivotFields("Adjustment Date")
Set Field13 = PT13.PivotFields("Adjustment Date")
Set Field14 = PT14.PivotFields("Adjustment Date")


NewDate1 = Worksheets("netting asins").Range("B1").Value
NewDate2 = Worksheets("netting asins").Range("F1").Value
NewDate3 = Worksheets("netting asins").Range("J1").Value
NewDate4 = Worksheets("netting asins").Range("N1").Value
NewDate5 = Worksheets("netting asins").Range("R1").Value
NewDate6 = Worksheets("netting asins").Range("V1").Value
NewDate7 = Worksheets("netting asins").Range("Z1").Value
NewDate8 = Worksheets("netting asins").Range("AD1").Value
NewDate9 = Worksheets("netting asins").Range("AH1").Value
NewDate10 = Worksheets("netting asins").Range("AL1").Value
NewDate11 = Worksheets("netting asins").Range("AP1").Value
NewDate12 = Worksheets("netting asins").Range("AT1").Value
NewDate13 = Worksheets("netting asins").Range("AX1").Value
NewDate14 = Worksheets("netting asins").Range("BB1").Value
 
Upvote 0
Code: 
....
Set PT9 = Worksheets("netting asins").PivotTables("Thursday-Day")
Set PT10 = Worksheets("netting asins").PivotTables("Thursday-Night")
[COLOR=#ff0000][B]Set PT11 = ...?
[/B][/COLOR]Set PT12 = Worksheets("netting asins").PivotTables("Friday-Day")
Set PT12 = Worksheets("netting asins").PivotTables("Friday-Night")
Set PT13 = Worksheets("netting asins").PivotTables("Saturday-Day")
Set PT14 = Worksheets("netting asins").PivotTables("Saturday-Night")
 
Upvote 0
Hi tmsmyer,

I believe your issue is because you set the PT12 object twice, instead of the PT11 object.

Set PT10 = Worksheets("netting asins").PivotTables("Thursday-Night")
Set PT12 = Worksheets("netting asins").PivotTables("Friday-Day") <-----Change PT12 to PT11
Set PT12 = Worksheets("netting asins").PivotTables("Friday-Night")
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
"Out of Memory" Error when trying to pivot
Replies
2
Views
87
rachel06
R
S
  • Question Question
Simplified VBA code to generate faster output
Replies
1
Views
407
shansakhi
S
A
  • Question Question
Pivot Table VBA Help
Replies
0
Views
630
ashley1984
A
S
  • Solved
VBA + Pivot Tables = Pain
Replies
1
Views
761
snuffnchess
S
P
  • Solved
Need help with VBA code prompting Compile error: Next without For
2
Replies
15
Views
568
Pi_Lover
P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top