Free Microsoft Excel 2013 Quick Reference

Run-time error 1004 Table Cannot Overlap A Range...

I used the macro recorder in Excel 2007 to record the code below. The code is supposed to use a .iqy to pull data from a SharePoint 2007 site. When I run the macro, I receive the following error: A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table. I have no issue running the .iqy query from excel without using the macro. Any idea what's causing the 1004 error?
Sub Macro1()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;", Destination:=Range("A1") _
).QueryTable
.CommandType = 5
.CommandText = Array( _

"{9848425C-7548-45D7-97F6-0F39962103AD}{F0102C5F-57BF-4023-B2D0-FF93F04A0030} , _

"LISTNAME>http://prod65-share2.mgn.netl.doe.gov:21928/_vti_bin , "R>/Lists/Heat Survey")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"I:CommonHELPDESKMetric ReportsHeatSurveyQuery.iqy"
.ListObject.DisplayName = "Table_HeatSurveyQuery"
.Refresh BackgroundQuery:=False
End With

End Sub


Post your answer or comment

comments powered by Disqus

Related Results

  1. Run-time error 1004 Table Cannot Overlap A Range...
  2. Fix Runtime error 1004:A table cannot overlap a range that contains a PivotTableReport...
  3. Data import to table - causes run-time error (1004)
  4. Run-time Error 1004 - Macro only works with the 1st file which it was programmed with
  5. Run-time error 1004 - Cannot copy sheet to new file
  6. Run-time error 1004: Cannot change this part of a PivotTable report
  7. I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable
  8. Run time error (1004) cannot change part of merged cell
  9. Run-time error 1004 cannot access.
  10. Request for assist with Run-time error '1004' VB Pivot Table
  11. Run-time error 1004: Cannot shift nonblank cells off the worksheet
  12. Range.offset generates a Run Time error 1004
  13. Range.offset generates a Run Time error 1004
  14. Question about VBA amortization table Run-time Error '1004'
  15. Run-time error 1004: Cannot shift nonblank cells off the worksheet
  16. Run Time Error - 1004 / Pivot Tables
  17. Run time error 1004 when using macro to change pivot Table fields.
  18. Run-time error 1004 unable to get the pivotfields property of the pivot table class
  19. Trapping run-time error 1004 when web query fails
  20. Run-time error '1004' saving worksheet
  21. VBA Code prompted a Run-time error "1004" MSG
  22. Run-time error 1004 after pivotfields have details hidden + spin button
  23. Calculation Method (Run time error 1004)
  24. Run time error 1004
I used the macro recorder in Excel 2007 to record the code below. The code is supposed to use a .iqy to pull data from a SharePoint 2007 site. When I run the macro, I receive the following error: A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table. I have no issue running the .iqy query from excel without using the macro. Any idea what's causing the 1004 error?

Sub Macro1()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;", Destination:=Range("A1"))
.QueryTable
.CommandType = 5
.CommandText = Array( "{9848425C-7548-45D7-97F6-0F39962103AD}{F0102C5F-57BF-4023-B2D0-FF93F04A0030}

Hi,

I m using Excel 2010.
I used a macro recording to record the activity i.e., pull the data from a iqy file(web query file generated when you do Export to Excel in a SharePoint site to export SharePoint list data to Excel.). I want to run this macro on daily basis. However the problem is when you try to re- run the same macro I get error Run-time error '1004'
A table cannot overlap a range that contains a PivotTable Report, query results, protected cells or another table.

Any reasons why?

What would be the solution to my problem? Thanks.

RunTimeErr.JPG

Sub
Macro4()
'
' Macro4 Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;", Destination:=Range("$A$1") _
        ).QueryTable
        .CommandType = 5
        .CommandText = Array( _
       
"<LIST><VIEWGUID>{50A58D53-347D-4E68-B9BC-CA834F7A068E}</VIEWGUID><LISTNAME>{A486016E-80B2-44C3-8B4A-8394574B9430}</"
_
        , _
       
"LISTNAME><LISTWEB>http://pd2012/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLDER>/Lists/Projects
List</RO" _
        , "OTFOLDER></LIST>")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:Usersabcd2Downloadsowssvr.iqy"
        .ListObject.DisplayName = "Table_owssvr"
        .Refresh BackgroundQuery:=False
    End With
    
End Sub


Hi All,

I am importing a .csv file to a table. The table comprises two parts (1) the data dump and (2) the trimmed data together with specific formulae.

When the macro is run I receive a 'Run-time error 1004' message "Cannot complete operation: a table cannot overlap with a table". I have tried it manually and receive the same error message.

How do you import data to a table - that requires trimming, manipulation and dynamic ranges - without this occurring? I would appreciate advice.

Thanks in advance.

Kind regards,
David

Hi everyone, this is my first post. Iīm kinda new in programming in VBA and this is actually the 2nd Macro i created.
Iīm trying to copy 1 specific pivot table from a root file (which has sevaral sheets, but only 2 pivots) in a separate new workbook and sheet, and filter the pivot table values as needed. This proccess is used to create a monthly report which is generated with a different root file each time. The format of the root file is always the same, pivot fields are the same each time, and only the values change. When i programmed this initially, with this one file, it worked ok, and it still does when re testing. The issues comes up when i use any onther new file needed to generate a new report. All the errors are related to the famous:
Run-time error '1004'
Unable to set the Position property of the PivotField class
And is related to this code line:


	VB:
	
 ActiveSheet.PivotTables("PivotTable2").PivotFields("Waste") 
    .Orientation = xlColumnField 
    [COLOR=red].Position = 1[/COLOR] [COLOR=red]

Hello, I'm trying to copy, using code, a sheet to another workgroup.
I make this steps: Create a new file with name "file1.xlsx" and select the "totais" sheet from the "oldfile.xlsx" and when the code do the move command it gives an error : "run-time error 1004: Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workgroup...".
They advise to copy only the cells that have data.
My source file has 1048576 rows by 'XFD' columns and the destination only has 65536rows by 'IV' columns.
i don't know why my source file has so many columns and rows. I don't need them...
Any help?
Code:

If NumTrab = 0 Then 'Se seleccionou TUDO ent&#227;o faz:
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=TempFile, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
TempFile = ActiveWorkbook.Name
Windows(FicheiroActual).Activate
If Cb_5.Value = True Then 'TOTAIS ANO
Call TotaisAno
Sheets("TOTAIS").Move Before:=Workbooks(TempFile).Sheets(1)
End If
If Cb_2.Value = True Then 'Horas Trabalhadas
For x = 0 To UBound(ListaPessoal)
Nome = ListaPessoal(x, 1)
Call FazFolhaPessoal(Nome)
If FolhaInexistente = False Then
Workbooks(FicheiroActual).Sheets(ListaPessoal(x, 1)).Move After:=Workbooks(TempFile).Sheets(1)
End If
Next x
End If
If Cb_4.Value = True Then 'Horas de Acerto Mensais
Call Calcula_HA_Mensal
Workbooks(FicheiroActual).Sheets("Horas Acerto Mensal").Move Before:=Workbooks(TempFile).Sheets(1)
End If
If Cb_3.Value = True Then 'HCI's Mensais
Call Calcula_HCI_Mensal
Workbooks(FicheiroActual).Sheets("HCI Mensal").Move Before:=Workbooks(TempFile).Sheets(1)
End If

Workbooks(TempFile).Save 'Guarda Ficheiro
Workbooks(TempFile).Close
A = MsgBox("Dados gravados com sucesso no ficheiro " & TempFile, vbInformation + vbOKOnly)
Exit Sub
End If

Thank you,

M&#225;rio

I have a report that I run on a weekly basis and have not had an issue with it until it started pulling in August data. It's a set of three pivot tables that pull data in from an Access database. Attached is a snapshot of the report with the three tabs... the first tab runs fine but when I try to run the second and third I get the following error message:

"run-time error 1004: Cannot change this part of a PivotTable report"

When I go in to debug the error it brings me to the following line in the code:

'add YTD header
Cells(lProw + 1, iLcol + 1).Value = "YTD"

I have a feeling that since we're now in August and theres three columns per month we're going over the 26 columns, as referenced at the bottom of the code. But I'm not sure where to go from there.

Here's the code
------------------------------------
Sub AddYTDColumns()
Dim iCol As Integer
Dim iEcol As Integer 'last column for month
Dim iFcol As Integer 'first month column
Dim iLcol As Integer 'last column
Dim iSums As Integer
Dim lDrow As Long 'first data row
Dim lLrow As Long 'last row of ptable
Dim lProw As Long '1st ptable row
Dim lRow As Long
Dim sAcell As String 'activecell address
Dim pt As PivotTables
Dim wS As Worksheet
Dim vSums() As Variant

Set wS = Sheets(ActiveSheet.Name)
Set pt = wS.PivotTables
'exit if no pivot tables on active sheet
If pt.Count = 0 Then Exit Sub

'store activecell to be reselected at end
sAcell = ActiveCell.Address

'first row of pivot table
lProw = pt.Item(1).TableRange1.Row
'first row of data (hours) area
lDrow = pt.Item(1).DataBodyRange.Row

'get pivot table address
i = pt.Item(1).TableRange1.Address
'find last $
j = InStrRev(i, "$")
'last row of pivot table
lLrow = Mid(i, j + 1)
'last column of pivot table
k = InStr(i, ":$")
l = Mid(i, k + 2, Len(j - k + 2))
iLcol = Columns(l).Column

'clear current YTD columns
ClearYTD lProw, lLrow, iLcol

'find first month (data area)
iFcol = pt.Item(1).DataBodyRange.Column
'row w/ hourtypes
lRow = lProw + 2

x = Cells(lRow, iFcol)
For iEcol = iFcol + 1 To iLcol
Y = Cells(lRow, iEcol).Value
If Y = x Or Y = "" Then
iEcol = iEcol - 1
Exit For
End If
Next

'add YTD header
Cells(lProw + 1, iLcol + 1).Value = "YTD"
'copy & paste hourtype headers from first month
Range(Cells(lRow, iFcol), Cells(lRow, iEcol)).Copy
Cells(lRow, iLcol + 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False

'calc # of types, create array with sum formulas for each type
iSums = iEcol - iFcol

ReDim vSums(iSums)

For i = 0 To UBound(vSums)
vSums(i) = "=SUM("
Next

'loop thru months, create a total sum for each type
For iEcol = iFcol To iLcol Step iSums + 1
For i = 0 To UBound(vSums)
'add type column to sum statement, for last month add ')', otherwise add a ','
vSums(i) = vSums(i) & ColLtr(iEcol + i) & lDrow & _
IIf(iEcol >= iLcol - iSums, ")", ",")
Next
Next

'add formulas from array to 1st row of YTD column, use autofill to copy formulas
' to other cells in columns
For iCol = iLcol + 1 To iLcol + iSums + 1
i = iCol - (iLcol + 1)
Cells(lDrow, iCol).Formula = vSums(i)
Cells(lDrow, iCol).Select
x = ColLtr(iCol)
Selection.AutoFill Destination:=Range(x & lDrow & ":" & x & lLrow)
Next

'copy formats from first month to YTD columns
iEcol = iFcol + iSums
lRow = lProw + 1

Range(ColLtr(iFcol) & lRow & ":" & ColLtr(iEcol) & lLrow).Select
Selection.Copy
Cells(lRow, iLcol + 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Range(ColLtr(iLcol + 1) & lRow & ":" & ColLtr(iLcol + iSums + 1) & lLrow).Select
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous

'store last row
lLPrevRow = lLrow
Range(sAcell).Select

End Sub

Private Sub ClearYTD(lProw As Long, ByVal lLrow As Long, iLcol As Integer)
'clear YTD columns
Dim iCol As Integer
Dim iEcol As Integer
Dim lRow As Long

'determine last row
If lLPrevRow > lLrow Then lLrow = lLPrevRow

'find YTD columns
lRow = lProw + 1
For iCol = iLcol + 1 To 256
If Cells(lRow, iCol).Value = "YTD" Then
lRow = lRow + 1
'if YTD found then determine # of YTD columns to be cleared
For iEcol = iCol To 256
If Cells(lRow, iEcol).Value = "" Then
If iEcol > iLcol + 1 Then iEcol = iEcol - 1
Exit For
End If
Next
If iEcol >= 256 Then iEcol = iCol + 1
Exit For
End If
Next

If iCol >= 256 Then Exit Sub

'select YTD columns
Range(ColLtr(iLcol + 1) & lProw & ":" & ColLtr(iEcol) & lLrow).Select

'clear data
Selection.ClearContents
'clear formating
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = xlAutomatic
End With
NoBorders
End Sub

Private Sub NoBorders()
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

'****************************************************************************************
'translate passed column# into it's letter equivalent, uses Chr function to change number
' to letter (Ascii A = 65, Z = 90 so add 64), for columns over 26 divide the number by
' 26, the first letter will be the quotient the second will be the remainder (mod)
Public Function ColLtr(ByVal iColNo As Integer) As String
Dim iMod As Integer
Dim sCol As String

If iColNo >= 1 And iColNo <= 26 Then
sCol = Chr(iColNo + 64)
Else
iMod = iColNo Mod 26
If iMod = 0 Then iMod = 26
iColNo = iColNo - iMod
iColNo = iColNo / 26
sCol = Chr(iColNo + 64) + Chr(iMod + 64)
End If

ColLtr = sCol

End Function

I tried to get around the problem of the pivot table field setting
defaulting to Count instead of Sum by running a macro of change the
setting from Count to Sum. However, when I tried to run the Macro, I
got error message of run time error 1004, unable to get the pivot
fields property of the pivot field class.

Well, is there any way around this?

I am getting run time error 1004 - cannot change part of merged cell.
what the code does is looks for the last entry in column B of In-Process sheet and locates that particular number in sheet Temp. then it copies everything after that entry into In-Process.
When I give a number, which is not in temp sheet, it gives me the no new records message. but however when I give the number present in the temp, it gives run time error. I have limited knowledge in VB so please help. thanks

PHP Code:
 Dim fnd As Range    Dim lr As Long    Dim i As Integer
    i = Sheets("In-Process").Range("B" & Rows.Count).End(xlUp).Value    lr = Range("A" & Rows.Count).End(xlUp).Row    Set fnd = Range("B:B").Find(i)            If Not fnd Is Nothing Then            Range(Cells(fnd.Row, 1), Cells(lr, 25)).Copy Sheets("In-Process").Range("A" & Rows.Count).End(xlUp)(2)        Else            MsgBox " No new records"        End If 


When i click on my req. to go to new requisition it gives me a run-time error 1004 cannot access read only document A70001.xls how do i fix this?

HELP!

Hello. This is my first post and I would greatly appreciate any assistance offered with an error I am receiving in a VB script in Excel.

I am very new to using any visual basic so I used an example from Debra Dalgleish's site "contextures.com although the code appears to have been written by a "Robert Rosenburg".

The code is intended to update other pivot tables with a field you are filtering for. The original code called for two fields acrross three tables. My needs are for three fields accross six tables (including the target).

When I attempt to execute the code (it is attached to the sheet as an "option explicit" which I take to means is watching for activity within the sheet) I get:

Run-time error '1004':
Unable to get the PivotFields property of the PivotTable class

The code appears to be breaking on this line:
Set pf1 = pt.PivotFields(strField1)

I did a little reading on others who had this error (that brought me to this site) and I have a vague idea that either Excel is having problem caching the data to be written or maintaining focus on the page and/or field.

I realize I am way out of my league on this and please forgive any wrong assumtions I have made. I can provide the original code and anything else that helps although the data I am working with specifically can't be shared.

Here is the code I have modified ( I put asterisks where the actual field names are):

Option Explicit

Dim mvPivotPageValue1 As Variant
Dim mvPivotPageValue2 As Variant
Dim mvPivotPageValue3 As Variant

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
''based on code by Robert Rosenberg posted 2000/01/11
''A module level variable (mvPivotPageValue) keeps
''track of the last selection from the Page Field.
Dim wsOther1 As Worksheet
Dim wsOther2 As Worksheet
Dim wsOther3 As Worksheet
Dim wsOther4 As Worksheet
Dim wsOther5 As Worksheet
Dim pt As PivotTable
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim pt3 As PivotTable
Dim pt4 As PivotTable
Dim pt5 As PivotTable

Dim pf1 As PivotField
Dim pf2 As PivotField
Dim pf3 As PivotField
Dim strField1 As String
Dim strField2 As String
Dim strField3 As String

strField1 = "****"
strField2 = "****"
strField3 = "****"

Set wsOther1 = Sheets("***")
Set wsOther2 = Sheets("***")
Set wsOther3 = Sheets("***")
Set wsOther4 = Sheets("***")
Set wsOther5 = Sheets("***")
Set pt = Target
Set pt1 = wsOther1.PivotTables(1)
Set pt2 = wsOther2.PivotTables(1)
Set pt3 = wsOther3.PivotTables(1)
Set pt4 = wsOther4.PivotTables(1)
Set pt5 = wsOther5.PivotTables(1)
Set pf1 = pt.PivotFields(strField1)
Set pf2 = pt.PivotFields(strField2)
Set pf3 = pt.PivotFields(strField3)

On Error Resume Next
If LCase(pt.PivotFields(strField1).CurrentPage) LCase(mvPivotPageValue1) Then
'The PageField1 was changed
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue1 = pt.PivotFields(strField1).CurrentPage
pt1.PageFields(strField1).CurrentPage = mvPivotPageValue1
pt2.PageFields(strField1).CurrentPage = mvPivotPageValue1
pt3.PageFields(strField1).CurrentPage = mvPivotPageValue1
pt4.PageFields(strField1).CurrentPage = mvPivotPageValue1
pt5.PageFields(strField1).CurrentPage = mvPivotPageValue1
Application.EnableEvents = True
End If

If LCase(pt.PivotFields(strField2).CurrentPage) LCase(mvPivotPageValue2) Then
'The PageField2 was changed
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue2 = pt.PivotFields(strField2).CurrentPage
pt1.PageFields(strField2).CurrentPage = mvPivotPageValue2
pt2.PageFields(strField2).CurrentPage = mvPivotPageValue2
pt3.PageFields(strField2).CurrentPage = mvPivotPageValue2
pt4.PageFields(strField2).CurrentPage = mvPivotPageValue2
pt5.PageFields(strField2).CurrentPage = mvPivotPageValue2
Application.EnableEvents = True
End If

If LCase(pt.PivotFields(strField3).CurrentPage) LCase(mvPivotPageValue3) Then
'The PageField3 was changed
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue3 = pt.PivotFields(strField3).CurrentPage
pt1.PageFields(strField3).CurrentPage = mvPivotPageValue3
pt2.PageFields(strField3).CurrentPage = mvPivotPageValue3
pt3.PageFields(strField3).CurrentPage = mvPivotPageValue3
pt4.PageFields(strField3).CurrentPage = mvPivotPageValue3
pt5.PageFields(strField3).CurrentPage = mvPivotPageValue3
Application.EnableEvents = True
End If

End Sub

I have a macro running that creates a row, runs calculations, makes
references (values, not links), then deletes the row.

It works fine for several runs, but then I get this 1004 error.

"Run-time error '1004':

To prevent posssible loss of data, Microsoft Office Excel cannot shift
nonblank cells off the worksheet.

Try to locate the last nonblank cell by pressing CTRL+END, and delete
or clear all in cells between the last cell and the end of your data.
Then select cell A1 and save your workbook to reset the last cell
used."

If I save the file and exit out, it works fine for a few more runs. Its
a macro that will only be ran 1-2x per week, but still I want to make
sure this wont pose an issue several months down the road when someone
is performing data entry. I won't post the entire code, because it was
recorded, and incredibly long..but if you feel that would help I can
post it up.

FWIW, this is the actual piece of code giving the error:
Selection.Insert Shift:=xlToRight

Hi,

In the code below I'm getting a 'Run Time error 1004: application
defined or object-defined error when I try offset a range by a value
greater than 32231. Can anybody explain why this happening. I'm
trying to append some data to the end of a dynamic range, can I use
something else instead of range.offset?

Sub AddNewLogs(MyLogArr())

Dim rngRefLogs As Range
Dim rngRefLogsDateCol As Range
Dim rngTemp As Range
Dim LastRow As Long
Dim LastCol As Integer

'activate the logs worksheet
Sheets("Logs").Activate
'set the dynmaic range
Set rngRefLogs = Range("RefLogs")
'get the lastrow in Column A
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Set temporary range offset from the end of our dynamic range by the
number of rows it contains
Set rngTemp = rngRefLogs.Offset(LastRow - 1, 0)
'extend the temporary range by the number records in our array
Set rngTemp = rngTemp.Resize(UBound(MyLogArr, 1) - LBound(MyLogArr, 1)
+ 1, 6)
'set the temporary range to the value of the array
rngTemp.Value = MyLogArr
'refresh the pivot table
ActiveWorkbook.RefreshAll
'save the work book
ActiveWorkbook.Save
'switch focus to the analysis sheet
Sheets("Analysis").Activate

End Sub

Thanks

Hi,

In the code below I'm getting a 'Run Time error 1004: application
defined or object-defined error when I try offset a range by a value
greater than 32231. Can anybody explain why this happening. I'm
trying to append some data to the end of a dynamic range, can I use
something else instead of range.offset?

Sub AddNewLogs(MyLogArr())

Dim rngRefLogs As Range
Dim rngRefLogsDateCol As Range
Dim rngTemp As Range
Dim LastRow As Long
Dim LastCol As Integer

'activate the logs worksheet
Sheets("Logs").Activate
'set the dynmaic range
Set rngRefLogs = Range("RefLogs")
'get the lastrow in Column A
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Set temporary range offset from the end of our dynamic range by the
number of rows it contains
Set rngTemp = rngRefLogs.Offset(LastRow - 1, 0)
'extend the temporary range by the number records in our array
Set rngTemp = rngTemp.Resize(UBound(MyLogArr, 1) - LBound(MyLogArr, 1)
+ 1, 6)
'set the temporary range to the value of the array
rngTemp.Value = MyLogArr
'refresh the pivot table
ActiveWorkbook.RefreshAll
'save the work book
ActiveWorkbook.Save
'switch focus to the analysis sheet
Sheets("Analysis").Activate

End Sub

Thanks

I am trying to get a macro in VBA excel to create an amortization table for me. I have written what I believe is the right code, but I keep getting an error message. It gives me a Run-time Error '1004' : Application-defined or object defined error. I made the labels for the table with Period, Payment, Interest, Principle, and Balance. When I run the macro it will show an input box for "Enter rate of loan in decimal form", then "Enter number of years", then "Enter the amount of loan" and then it shows the error. The macro is supposed to fill in the table under the headings based on what is put in the input cells. Am I missing anything or not defining the right variables?

Here is what I have:

Option Explicit

Public Sub Amortization()

Dim Rate As Long
Dim Amount As Currency
Dim Time As Currency
Dim Payment As Currency
Dim Balance As Currency
Dim X As Currency
Dim Y As Currency
Dim MPayment As Currency
Dim Principal As Currency


Rate = InputBox("Enter rate of loan in decimal from", "RATE")
Range("a1").Value = "Rate of Loan"
Range("b1").Value = FormatPercent(Rate, 2)

Time = InputBox("Enter the number of years", "Time")
Range("a2").Value = "Number of Years"
Range("b2").Value = Time

Amount = InputBox("Enter the Amount of Loan", "AMOUNT")
Range("a3").Value = "Amount Borrowed"
Range("b3").Value = FormatCurrency(Amount, 2)

Range("a4").Value = "Amount of Monthly Payment"
Range("b4").Value = FormatCurrency(-Pmt(Rate / 12, Time * 12, Amount), 2)

Range("c6").Value = "Period"
Range("d6").Value = "Payment"
Range("e6").Value = "Interest"
Range("f6").Value = "Principle"
Range("g6").Value = "Balance"
Range("a1:a4").Font.Bold = True
Range("c6:g6").Font.Bold = True
Payment = Range("b4").Value
Balance = Amount


For X = 1 To Time
Sheet1.Cells(Y, 3) = X
Sheet1.Cells(Y, 4) = FormatCurrency(MPayment, 2)
Sheet1.Cells(Y, 5) = FormatCurrency(-IPmt(Rate, X, Time, Amount), 2)
Principal = FormatCurrency(-PPmt(Rate, X, Time, Amount), 2)
Sheet1.Cells(Y, 6) = FormatCurrency(Principal, 2)
Y = Y + 1
Next



End Sub


Here is what I am supposed to do to create the macro. I believe I might have missed some variables that need to be defined, as hinted in question 6. If you can help I greatly appreciate it!

Create a macro called Amortization with a button that will perform the following tasks:

1. Prompt the user to enter the rate of the loan in decimal form and write label Rate of Loan in A1 with the value in B1 formatted as a percentage.

2. Prompt the user to enter the number of years for the loan and write label Number of Years for the loan in A2 with the value in B2 formatted as currency.

3. Prompt the user to enter the amount of the loan and write label Amount Borrowed in A3 with the value in B3 formatted as currency.

4. Write the label Payment in A4 with the actual value in B4 formatted as currency. Use the PMT function for this calculation.

5. Create an amortization table with the following headings and format the values with currency style formatting:

a. Period-this will reflect the number of payments.

b. Payment-use the PMT function to calculate monthly payment

c. Interest-use the IPMT function to calculate monthly interest.

d. Principal-use the PPMT function to calculate monthly principal.

e. Balance-first time subtract the principal from loan amount. Afterwards, you will use the old balance minus the current principal to calculate the new balance.

6. Creation of the amortization table will require a While or For loop where the rate must be divided by 12 and the years of loan multiplied by 12. You should create variables for Rate, Time, Amount, and Payment. It will also help to create variables for Period, Interest, Principal, and Balance.

7. Make sure your macro also widens columns a-g to an appropriate width.

8. You should bold all labels.

9. Create a Clear Macro with a button that will clear the spreadsheet.

I have a macro running that creates a row, runs calculations, makes
references (values, not links), then deletes the row.

It works fine for several runs, but then I get this 1004 error.

"Run-time error '1004':

To prevent posssible loss of data, Microsoft Office Excel cannot shift
nonblank cells off the worksheet.

Try to locate the last nonblank cell by pressing CTRL+END, and delete
or clear all in cells between the last cell and the end of your data.
Then select cell A1 and save your workbook to reset the last cell
used."

If I save the file and exit out, it works fine for a few more runs. Its
a macro that will only be ran 1-2x per week, but still I want to make
sure this wont pose an issue several months down the road when someone
is performing data entry. I won't post the entire code, because it was
recorded, and incredibly long..but if you feel that would help I can
post it up.

FWIW, this is the actual piece of code giving the error:
Selection.Insert Shift:=xlToRight

HI All

I have created a Macro for Pivot Tables and when am trying to apply the same code in different sheet its showing the run time error 1004.

Can someone please check the below code and let me know if any changes to be made also if possible can someone make changes to it.

Sub pv() 
    Dim PT As PivotTable, PF As PivotField 
     
    Sheets("RD").Activate 
    FinalRow = Cells(Rows.Count, 5).End(xlUp).Row 
    FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column 
    Set LastCell = Cells(FinalRow, FinalCol) 
    ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Range("A1", LastCell) 
     ' start to create pivot table
    Sheets.Add 
    ActiveSheet.Name = "pivot_summary" 
    Sheets("pivot_summary").Select 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ 
    "RD!MyRange", Version:=xlPivotTableVersion10).CreatePivotTable _ 
    TableDestination:="R3C1", TableName:="PT", DefaultVersion _ 
    :=xlPivotTableVersion10 
    With ActiveSheet.PivotTables("PT").PivotFields("I1 B Contract No.") 
        .Orientation = xlRowField 
        .Position = 1 
    End With 
    With ActiveSheet.PivotTables("PT").PivotFields("C1 C Customer Name") 
        .Orientation = xlRowField 
        .Position = 2 
    End With 
     
    With ActiveSheet.PivotTables("PT").PivotFields("Negotiated Amount OPR") 
        .Orientation = xlColumnField 
        .Position = 1 
    End With 
     
     
    With ActiveSheet.PivotTables("PT").PivotFields("OP Actual Backlog") 
        .Orientation = xlColumnField 
        .Position = 1 
    End With 
     
    With ActiveSheet.PivotTables("PT").PivotFields("CQ Rev") 
        .Orientation = xlColumnField 
        .Position = 1 
    End With 
     
    With ActiveSheet.PivotTables("PT").PivotFields("12 M Conv Rev") 
        .Orientation = xlColumnField 
        .Position = 1 
    End With 
End Sub


Hi All,

I have arranged data using a pivot table and want to display the data
using a pivot chart, which I have been able to do.

I have recorded a basic macro which should (in theroey) change the Value field in the pivot table. However when i run the macro I get the following error message:
Run-Time error 1004
Unable to get the PivotFields property of the Pivoit Table Class.
The code is as follows:
Sub Water()
'
' Water Macro

    Sheets("Sheet1").Select
    Range("I2").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Result"). _
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Water"), "Count of Water", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False
    Sheets("Flux Levels").Select
End Sub
When I run the deubg option the error seems to be occuring on the 3rd and 4th lines of the code.

Any suggestions?

Thanks!

Hi
I am a novice at this... have recorded macros and used them but come stuck.

Using Office 2007 (Windows 7 Pro)
Have Data on Sheet one from SQL via ODBC, works well. (Sales Data)

Have a Pivot Table called PivotTable2
Column B has the shipped date and I want to create buttons so user can filter by last month, last week, this week etc.

Recorded a macro and get "run-time error 1004 unable to get the pivotfields property of the pivot table class"

The query is;-

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveSheet.PivotTables("PivotTable2").PivotFields("Shipment Date"). _
PivotFilters.Add Type:=xlDateLastWeek
End Sub

How can I get this to run. I also want same for last week, moth etc.

Thanks in advance
arun soni

Hello All,

I am trying to trap run-time error 1004 Unable to open http://... that occurs when I am running a series of web queries. The error occurs because the website I am querying goes down temporarily so the web query is unable to connect with it. I have verified this by pasting the URL into a regular browser window. The browser cannot make connection with the particular URL or the home page.

My code is working fine when the site is up, but when it is temporarily down, my code throws the 1004 error and waits for me to hit debug and then F5 to restart my code. I would like to trap this error and restart the code automatically. I have the following code that I pieced together from various contributors and sites, but it is not trapping the error.


	VB:
	
 QueryTheSite() 
     
     
Retry: 
    On Error Goto ErrTest 
    With ActiveSheet.QueryTables.Add(Connection:=myURL, Destination:=Cells(TopRow, 2)) 
        .Name = myTableName 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = False 
        .RefreshPeriod = 0 
        .WebSelectionType = xlSpecifiedTables 
        .WebFormatting = xlWebFormattingNone 
        .WebTables = """obsTable""" 
        .WebPreFormattedTextToColumns = True 
        .WebConsecutiveDelimitersAsOne = True 
        .WebSingleBlockTextImport = False 
        .WebDisableDateRecognition = False 
        .Refresh BackgroundQuery:=False 
    End With 
     
     
    Exit Sub 
ErrTest: 
    If Err.Number  1004 Then 
        MsgBox Err.Number & " " & Err.Description 
    Else 
        Application.Wait Now + TimeValue("00:00:05") 
        Goto Retry 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please help. I would like to run the bulk of these queries overnight, and no one is there to monitor the process and respond to this error. Thank you for your help!

BTW: myTableName = "observation detail"
myURL and TopRow are dynamic, but an example URL is http://www.wunderground.com/weathers...ay=8&year=2010

Thanks again!

I have a workbook that runs through a list of teams and saves each team's unique data into it's own workbook. The code was running perfect when I was running it from my desktop and saving the files on my local C drive. When I put the file on another drive and ran through the macro, I get this run-time error everytime it attempts to save. Does anyone have any idea why?

The WbNew.SaveAs line is highlighted when I debug the code.

Run-time error '1004':

Microsoft Office Excel cannot acces the file 'G:ReportsBy Team'. There are several possible reasons:
The file name or path does not exist.The file is being used by another program.The workbook you are trying to save has the same name asWhen I hit debug this line is highlighted.


	VB:
	
wbNew.SaveAs ("G:ReportsBy Team" & ORGID & " Team " & Sup.Value & Format(Date, " mm-dd-yyyy")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
 
Public Sub Sort_Out_Sups() 
    Dim r As Range 
    Dim wsSource As Worksheet 
    Dim wbNew As Workbook 
    Dim rData As Range 
    Dim rData2 As Range 
    Dim Sup As Range 
    Dim wsVal As Worksheet 
    Dim wsSource2 As Worksheet 
    Dim ORGID As String 'ORG ID
    ORGID = Range("C2").Value 
     
    Set wsSource = Worksheets(1) 
    Set wsSource2 = Worksheets(2) 
    Set r = wsSource.Rows(1).Find("Sup") 
    Set rData = r.CurrentRegion 
    Set rData2 = wsSource2.Range("A1").CurrentRegion 
    Set r = r.Offset(1, 0).Resize(r.End(xlDown).Row - 1) 
    Set wsVal = Worksheets("Validation") 
    rData.AutoFilter 
    rData2.AutoFilter 
    For Each Sup In r.Rows 
        If Application.CountIf(r.Resize(Sup.Row - 1), Sup.Value) = 1 Then 
            rData.AutoFilter Field:=rData.Columns.Count, Criteria1:=Sup.Value 
            rData2.AutoFilter Field:=rData.Columns.Count, Criteria1:=Sup.Value 
            Set wbNew = Workbooks.Add 
            rData.Copy wbNew.Worksheets(1).Range("A1") 
            Columns("A:A").Select 
            Selection.ColumnWidth = 9 
            Columns("B:B").Select 
            Selection.ColumnWidth = 9 
            Columns("C:C").Select 
            Selection.ColumnWidth = 6.71 
            Columns("D:D").Select 
            Selection.ColumnWidth = 7.71 
            Columns("E:E").Select 
            Selection.ColumnWidth = 5.86 
            Columns("F:F").Select 
            Selection.ColumnWidth = 5.57 
            Columns("G:G").Select 
            Selection.ColumnWidth = 5.43 
            Columns("H:H").Select 
            Selection.ColumnWidth = 9 
            Columns("I:I").Select 
            Selection.ColumnWidth = 11.29 
            Columns("J:J").Select 
            Selection.ColumnWidth = 3 
            Columns("K:K").Select 
            Selection.ColumnWidth = 8.86 
            Columns("L:L").Select 
            Selection.ColumnWidth = 5.86 
            Columns("M:M").Select 
            Selection.ColumnWidth = 5.57 
            Columns("N:N").Select 
            Selection.ColumnWidth = 9.43 
            Columns("O:O").Select 
            Selection.ColumnWidth = 5.43 
            Columns("P:P").Select 
            Selection.ColumnWidth = 9 
            Columns("Q:Q").Select 
            Selection.ColumnWidth = 13.57 
            Columns("R:R").Select 
            Selection.ColumnWidth = 8 
            Columns("S:S").Select 
            Selection.ColumnWidth = 8.86 
            Columns("T:T").Select 
            Selection.ColumnWidth = 10.57 
            Columns("U:U").Select 
            Selection.ColumnWidth = 19.86 
            Columns("V:V").Select 
            Selection.ColumnWidth = 8 
            Columns("W:W").Select 
            Selection.ColumnWidth = 14.14 
            Columns("X:X").Select 
            Selection.ColumnWidth = 14.14 
            Columns("Y:Y").Select 
            Selection.ColumnWidth = 14.14 
            Columns("Z:Z").Select 
            Selection.ColumnWidth = 3.14 
            Columns("AA:AA").Select 
            Selection.ColumnWidth = 3.86 
            Columns("AB:AB").Select 
            Selection.ColumnWidth = 5.14 
            Columns("AC:AC").Select 
            Selection.ColumnWidth = 8.43 
            Columns("AD:AD").Select 
            Selection.ColumnWidth = 19.43 
            Columns("AE:AE").Select 
            Selection.ColumnWidth = 19.71 
            Range("A1").Select 
            wbNew.Worksheets(1).Name = wsSource.Name 
            rData2.Copy wbNew.Worksheets(2).Range("A1") 
             
            wbNew.Worksheets(2).Name = wsSource2.Name 
            wsVal.Copy after:=wbNew.Worksheets(wbNew.Worksheets.Count) 
            Application.DisplayAlerts = False 
            Worksheets("Sheet3").Delete 
            Application.DisplayAlerts = True 
            Sheets("Validation").Select 
            Range("A1:A25").Select 
            ActiveWorkbook.Names("validation").Delete 
            ActiveWorkbook.Names("validation").Delete 
            ActiveWorkbook.Names.Add Name:="validation", RefersToR1C1:= _ 
            "=Validation!R1C1:R25C1" 
            Sheets(2).Select 
            Columns("A:A").Select 
            Selection.ColumnWidth = 9 
            Columns("B:B").Select 
            Selection.ColumnWidth = 9 
            Columns("C:C").Select 
            Selection.ColumnWidth = 6.71 
            Columns("D:D").Select 
            Selection.ColumnWidth = 7.71 
            Columns("E:E").Select 
            Selection.ColumnWidth = 5.86 
            Columns("F:F").Select 
            Selection.ColumnWidth = 5.57 
            Columns("G:G").Select 
            Selection.ColumnWidth = 5.43 
            Columns("H:H").Select 
            Selection.ColumnWidth = 9 
            Columns("I:I").Select 
            Selection.ColumnWidth = 11.29 
            Columns("J:J").Select 
            Selection.ColumnWidth = 3 
            Columns("K:K").Select 
            Selection.ColumnWidth = 8.86 
            Columns("L:L").Select 
            Selection.ColumnWidth = 5.86 
            Columns("M:M").Select 
            Selection.ColumnWidth = 5.57 
            Columns("N:N").Select 
            Selection.ColumnWidth = 9.43 
            Columns("O:O").Select 
            Selection.ColumnWidth = 5.43 
            Columns("P:P").Select 
            Selection.ColumnWidth = 9 
            Columns("Q:Q").Select 
            Selection.ColumnWidth = 13.57 
            Columns("R:R").Select 
            Selection.ColumnWidth = 8 
            Columns("S:S").Select 
            Selection.ColumnWidth = 8.86 
            Columns("T:T").Select 
            Selection.ColumnWidth = 10.57 
            Columns("U:U").Select 
            Selection.ColumnWidth = 19.86 
            Columns("V:V").Select 
            Selection.ColumnWidth = 8 
            Columns("W:W").Select 
            Selection.ColumnWidth = 14.14 
            Columns("X:X").Select 
            Selection.ColumnWidth = 14.14 
            Columns("Y:Y").Select 
            Selection.ColumnWidth = 14.14 
            Columns("Z:Z").Select 
            Selection.ColumnWidth = 3.14 
            Columns("AA:AA").Select 
            Selection.ColumnWidth = 3.86 
            Columns("AB:AB").Select 
            Selection.ColumnWidth = 5.14 
            Columns("AC:AC").Select 
            Selection.ColumnWidth = 8.43 
            Columns("AD:AD").Select 
            Selection.ColumnWidth = 19.43 
            Columns("AE:AE").Select 
            Selection.ColumnWidth = 19.71 
            Range("A1").Select 
            Rows("1:1").Select 
            Selection.AutoFilter 
            Range("A1").Select 
            Sheets(1).Select 
            Rows("1:1").Select 
            Selection.AutoFilter 
            Range("A1").Select 
             
            wbNew.SaveAs ("G:ReportsBy Team" & ORGID & " Team " & Sup.Value & Format(Date, " mm-dd-yyyy")) 
             
            Application.DisplayAlerts = False 
            If Not ActiveWorkbook.MultiUserEditing Then 
                ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, _ 
                accessMode:=xlShared 
            End If 
            Application.DisplayAlerts = True 
             
            wbNew.Close 
            Set wbNew = Nothing 
        End If 
    Next 
    wsSource.ShowAllData 
    wsSource2.ShowAllData 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I got a "Run-time error '1004': Unable to get the PivotTables property of the Worksheet class" msg.


	VB:
	
 
Sub Macro1() 
     '
     ' Macro1 Macro
     '
     
     ' open file ANHUNB.txt
    ChDir "C:CPSI" 
    Workbooks.OpenText Filename:="C:CPSIANHUNB.TXT", Origin:=437, StartRow _ 
    :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(22, 1), Array _ 
    (30, 1), Array(40, 1), Array(50, 1), Array(63, 1), Array(77, 1), Array(83, 1), Array(96, 1), _ 
    Array(109, 1), Array(114, 1), Array(119, 1), Array(123, 1)), TrailingMinusNumbers:= _ 
    True 
     ' copy data
    Windows("ANHUNB.TXT").Activate 
    Range("A1").Select 
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 
    Selection.Copy 
     
    Windows("DNFB.ANH.xlsm").Activate 
    Sheets("detail").Select 
    Range("A1").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
     
     ' copy report date
    Windows("ANHUNB.TXT").Activate 
    Range("A1").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
     
    Windows("DNFB.ANH.xlsm").Activate 
    Sheets("main").Select 
    Range("A2").Select 
    ActiveSheet.Paste 
     
     ' copy title line
    Sheets("ref").Select 
    Range("M1:Y1").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
     
    Sheets("detail").Select 
    Range("A1").Select 
    ActiveSheet.Paste 
     
     ' refresh pivot
    Sheets("main").Select 
    Range("B32").Select 
    Application.CutCopyMode = False 
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh 
     
     'close txt file
     
     
    Windows("ANHUNB.TXT").Activate 
     '    Application.DisplayAlerts = False
    ActiveWorkbook.Close 
     '    Application.DisplayAlerts = True
     
     'Application.ScreenUpdating = False
     'Application.ScreenUpdating = True
     '77 to 79
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

If you guys could provide some guidance on the issue, I would greatly appreciate it. Thanks ahead of time!

Hi,

I am getting this message:

Run-time error 1004

The formula is not complete. Make sure an ending square bracket ] is not missing. When I press debug, it takes me to this line in the code:


	VB:
	
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Product[All;Total]", _ 
xlDataAndLabel, True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have a pivot table which can be re-arranged using VBA. This part of the code is used to select the totals of the Product field so that formatting can be applied.

When all pivotfields are shown in full detail, there is no problem. This arises if the any of the label fields have the details switched off.

At the moment, I am using such a code for the minimizing/maximizing:


	VB:
	
 minimize_Click() 
    Application.EnableEvents = False 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
     
    Range("A10").ShowDetail = False 
    Range("B10").ShowDetail = False 
     
    Application.EnableEvents = True 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have 3 such buttons, which minimize automatically to different levels.

I am asking for your help please in:

1) fixing this error. I can't figure out where I have to add the "]".

2) combining the 3 buttons' actions into one spin button. If possible, the spin should not rotate indefinitely. That is, "sub 1" should be the top spin. Clicking further should remain on "sub 1". Clicking one down on the spin should do "sub 2". Clicking down again to do "sub 3". Clicking down again does nothing because "sub 3" is the bottom limit.

Appreciate any and all the help.

Thanks,
Helen

I have asked a releated question in a earier post, and I have seach the post in this fourm. The same problem have ben asked a few times but seen no one have been answered it fully. Is this a bug on Excel with XP or.....
The problem is this: when I use

	VB:
	
Worksheets("wksTensionMax").Range.("rgnTensionCalculate").Calculate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It show the following error.
Run time error "1004"
calculate method of range failed. This is from the help manul
Calculates all open workbooks, a specific worksheet in a workbook, or a specified range of cells on a worksheet, as shown in the following table.

To calculate Follow this example
All open workbooks Application.Calculate (or just Calculate)
A specific worksheet Worksheets(1).Calculate
A specified range Worksheets(1).Rows(2).Calculate

expression.Calculate
expression Optional for Application, required for Worksheet and Range. An expression that returns an object in the Applies To list.

Example
This example calculates the formulas in columns A, B, and C in the used range on Sheet1.

Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate
Did I do somthings wrong? or is this this just a bug?

Thank your for reading this

hi everyone,

i recorded this macro to replace the data field of a pivot table i'm working on.


	VB:
	
Range("A15").Select 
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of CLPD_PRIM_AMT"). _ 
Orientation = xlHidden 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CLPB_PRIM_AMT") 
    .Orientation = xlDataField 
    .Position = 1 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but when i ran the macro this error came out:

run time error 1004:

unable to set the Orientation property of the PivotField class

i've checked the pivottable name and the field names if they're correct and they are.

any ideas what's happening here? appreciate your feedback

thanks

lem


No luck finding an answer? You could always try Google.