Run-time eror '1004';

G

Guest

I get this error when I press the Refresh Button on my spreadsheet to
generate a PivotTable.

This is what is on the Display:
This command requires at least two rows of source data. You cannot use the
command on a selection in only on row. Try the following:

If you're using an advanced filter, select a range of cells that contains at
least tow rows of data. Then click the Advanced Filter command again.

If you're creating a PivotTable report or PivotChart report, type a

My spreadsheet was designed in 95 and I have tried running this macro in a
spreadsheet saved as 95 and as 2003. And I still get the same error. I have
rebooted my machine.

Here is the code:

This is the line that is highlighted when I use Debug.

ActiveSheet.PivotTables("PivotTable3").RefreshTable

I had a similar issue with the same spreadsheet but a different PivotTable.

I removed the protection from the sheets involved. And now one is working
and the other is still failing.

Sub TotalChapterMeetings()
'

Application.ScreenUpdating = False
Sheets("Chapter Mtgs Pivot Table").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Selection.Sort Key1:="R3C2", Order1:=xlDescending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
Sheets("Chapter Meetings Details").Select
Application.ScreenUpdating = True
End Sub

Sub TotalBoardMeetings()
'update pivot table

'
Application.ScreenUpdating = False
Sheets("PCS Board Mtgs Pivot Table").Select
Range("C4").Select
ActiveSheet.PivotTables("PivotTable3").RefreshTable
If Range("a22").Value = 1 And Range("a23").Value = 1 Then
With ActiveSheet.PivotTables("PivotTable3").PivotFields("yes")
.PivotItems("(blank)").Visible = False
End With
End If
If Range("c4").Value < 1 Then GoTo 10
Selection.Sort Key1:="R4C3", Order1:=xlDescending,
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
10 Sheets("PCS Board Meetings Details").Select
Application.ScreenUpdating = True


End Sub

Any assistance would be appreciated.
Darlene
 
G

Guest

Hi Darlene -

My guess is that the first line of the error dialog is telling you the exact
cause of the problem. The very first thing to do is to verify that source
data exists for PivotTable3 as follows:

1. Immediately after you receive that error dialog, close it by clicking
[OK] and then find where PivotTable3 resides in your workbook.

2. Right-click on any cell in the PivotTable3 and choose PivotTable Wizard.

3. When the Wizard dialog opens, click the [Back...] button.

4. The Wizard should then highlight the datasource for PivotTable3. If
there is no data there, you should attempt to find out why before suspecting
any other problems.

Let us know what you find when you navigate to the datasource using the
steps above.
 
G

Guest

Jay,

When I did what you suggested it is only pointing to one cell of the
worksheet and not all the columns and rows. How do I change the range
without re-creating the pivottable?

Thanks for you help!!!
Darlene

Jay said:
Hi Darlene -

My guess is that the first line of the error dialog is telling you the exact
cause of the problem. The very first thing to do is to verify that source
data exists for PivotTable3 as follows:

1. Immediately after you receive that error dialog, close it by clicking
[OK] and then find where PivotTable3 resides in your workbook.

2. Right-click on any cell in the PivotTable3 and choose PivotTable Wizard.

3. When the Wizard dialog opens, click the [Back...] button.

4. The Wizard should then highlight the datasource for PivotTable3. If
there is no data there, you should attempt to find out why before suspecting
any other problems.

Let us know what you find when you navigate to the datasource using the
steps above.
--
Jay


Darlene said:
I get this error when I press the Refresh Button on my spreadsheet to
generate a PivotTable.

This is what is on the Display:
This command requires at least two rows of source data. You cannot use the
command on a selection in only on row. Try the following:

If you're using an advanced filter, select a range of cells that contains at
least tow rows of data. Then click the Advanced Filter command again.

If you're creating a PivotTable report or PivotChart report, type a

My spreadsheet was designed in 95 and I have tried running this macro in a
spreadsheet saved as 95 and as 2003. And I still get the same error. I have
rebooted my machine.

Here is the code:

This is the line that is highlighted when I use Debug.

ActiveSheet.PivotTables("PivotTable3").RefreshTable

I had a similar issue with the same spreadsheet but a different PivotTable.

I removed the protection from the sheets involved. And now one is working
and the other is still failing.

Sub TotalChapterMeetings()
'

Application.ScreenUpdating = False
Sheets("Chapter Mtgs Pivot Table").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Selection.Sort Key1:="R3C2", Order1:=xlDescending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
Sheets("Chapter Meetings Details").Select
Application.ScreenUpdating = True
End Sub

Sub TotalBoardMeetings()
'update pivot table

'
Application.ScreenUpdating = False
Sheets("PCS Board Mtgs Pivot Table").Select
Range("C4").Select
ActiveSheet.PivotTables("PivotTable3").RefreshTable
If Range("a22").Value = 1 And Range("a23").Value = 1 Then
With ActiveSheet.PivotTables("PivotTable3").PivotFields("yes")
.PivotItems("(blank)").Visible = False
End With
End If
If Range("c4").Value < 1 Then GoTo 10
Selection.Sort Key1:="R4C3", Order1:=xlDescending,
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
10 Sheets("PCS Board Meetings Details").Select
Application.ScreenUpdating = True


End Sub

Any assistance would be appreciated.
Darlene
Click to expand...
Click to expand...
 
G

Guest

Jay,

I figured it out. The Excel help actually walked me through it.

Thanks for your help.

Darlene

Darlene said:
Jay,

When I did what you suggested it is only pointing to one cell of the
worksheet and not all the columns and rows. How do I change the range
without re-creating the pivottable?

Thanks for you help!!!
Darlene

Jay said:
Hi Darlene -

My guess is that the first line of the error dialog is telling you the exact
cause of the problem. The very first thing to do is to verify that source
data exists for PivotTable3 as follows:

1. Immediately after you receive that error dialog, close it by clicking
[OK] and then find where PivotTable3 resides in your workbook.

2. Right-click on any cell in the PivotTable3 and choose PivotTable Wizard.

3. When the Wizard dialog opens, click the [Back...] button.

4. The Wizard should then highlight the datasource for PivotTable3. If
there is no data there, you should attempt to find out why before suspecting
any other problems.

Let us know what you find when you navigate to the datasource using the
steps above.
--
Jay


Darlene said:
I get this error when I press the Refresh Button on my spreadsheet to
generate a PivotTable.

This is what is on the Display:
This command requires at least two rows of source data. You cannot use the
command on a selection in only on row. Try the following:

If you're using an advanced filter, select a range of cells that contains at
least tow rows of data. Then click the Advanced Filter command again.

If you're creating a PivotTable report or PivotChart report, type a

My spreadsheet was designed in 95 and I have tried running this macro in a
spreadsheet saved as 95 and as 2003. And I still get the same error. I have
rebooted my machine.

Here is the code:

This is the line that is highlighted when I use Debug.

ActiveSheet.PivotTables("PivotTable3").RefreshTable

I had a similar issue with the same spreadsheet but a different PivotTable.

I removed the protection from the sheets involved. And now one is working
and the other is still failing.

Sub TotalChapterMeetings()
'

Application.ScreenUpdating = False
Sheets("Chapter Mtgs Pivot Table").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Selection.Sort Key1:="R3C2", Order1:=xlDescending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
Sheets("Chapter Meetings Details").Select
Application.ScreenUpdating = True
End Sub

Sub TotalBoardMeetings()
'update pivot table

'
Application.ScreenUpdating = False
Sheets("PCS Board Mtgs Pivot Table").Select
Range("C4").Select
ActiveSheet.PivotTables("PivotTable3").RefreshTable
If Range("a22").Value = 1 And Range("a23").Value = 1 Then
With ActiveSheet.PivotTables("PivotTable3").PivotFields("yes")
.PivotItems("(blank)").Visible = False
End With
End If
If Range("c4").Value < 1 Then GoTo 10
Selection.Sort Key1:="R4C3", Order1:=xlDescending,
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
10 Sheets("PCS Board Meetings Details").Select
Application.ScreenUpdating = True


End Sub

Any assistance would be appreciated.
Darlene
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Darlene -

Sorry for the slow reply; this thread was not responding on my system for
some reason.

Glad to hear you've reset your pivot table's sourcedata range. For others
experiencing a similar problem, follow the steps in my first post in this
thread and provide the PivotTable Wizard with the new range when prompted.

--
Jay


Darlene said:
Jay,

I figured it out. The Excel help actually walked me through it.

Thanks for your help.

Darlene

Darlene said:
Jay,

When I did what you suggested it is only pointing to one cell of the
worksheet and not all the columns and rows. How do I change the range
without re-creating the pivottable?

Thanks for you help!!!
Darlene

Jay said:
Hi Darlene -

My guess is that the first line of the error dialog is telling you the exact
cause of the problem. The very first thing to do is to verify that source
data exists for PivotTable3 as follows:

1. Immediately after you receive that error dialog, close it by clicking
[OK] and then find where PivotTable3 resides in your workbook.

2. Right-click on any cell in the PivotTable3 and choose PivotTable Wizard.

3. When the Wizard dialog opens, click the [Back...] button.

4. The Wizard should then highlight the datasource for PivotTable3. If
there is no data there, you should attempt to find out why before suspecting
any other problems.

Let us know what you find when you navigate to the datasource using the
steps above.
--
Jay


:

I get this error when I press the Refresh Button on my spreadsheet to
generate a PivotTable.

This is what is on the Display:
This command requires at least two rows of source data. You cannot use the
command on a selection in only on row. Try the following:

If you're using an advanced filter, select a range of cells that contains at
least tow rows of data. Then click the Advanced Filter command again.

If you're creating a PivotTable report or PivotChart report, type a

My spreadsheet was designed in 95 and I have tried running this macro in a
spreadsheet saved as 95 and as 2003. And I still get the same error. I have
rebooted my machine.

Here is the code:

This is the line that is highlighted when I use Debug.

ActiveSheet.PivotTables("PivotTable3").RefreshTable

I had a similar issue with the same spreadsheet but a different PivotTable.

I removed the protection from the sheets involved. And now one is working
and the other is still failing.

Sub TotalChapterMeetings()
'

Application.ScreenUpdating = False
Sheets("Chapter Mtgs Pivot Table").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Selection.Sort Key1:="R3C2", Order1:=xlDescending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
Sheets("Chapter Meetings Details").Select
Application.ScreenUpdating = True
End Sub

Sub TotalBoardMeetings()
'update pivot table

'
Application.ScreenUpdating = False
Sheets("PCS Board Mtgs Pivot Table").Select
Range("C4").Select
ActiveSheet.PivotTables("PivotTable3").RefreshTable
If Range("a22").Value = 1 And Range("a23").Value = 1 Then
With ActiveSheet.PivotTables("PivotTable3").PivotFields("yes")
.PivotItems("(blank)").Visible = False
End With
End If
If Range("c4").Value < 1 Then GoTo 10
Selection.Sort Key1:="R4C3", Order1:=xlDescending,
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
10 Sheets("PCS Board Meetings Details").Select
Application.ScreenUpdating = True


End Sub

Any assistance would be appreciated.
Darlene
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Run-time error '1004' General ODBC Error 1
Can I Sort (A-Z or Z-A) Pivot Table Data or Fields using VBA? 4
"Runtime error 1004 Application-defined or object defined error" on data sort 0
Tidying Recorded Macro Code 11
Sort variable range in descending order 3
Simple Do... Until problem 4
How to leave Selection.Sort Key open.... 6
sort macro not working 1

Top