AddFields Method Of The PivotTable Class Failed Passing Variables

Aug 20, 2007

I am trying to pass variables to a sub which creates a pivottable.

The following line of code works:

pt.AddFields RowFields:=Array("BG_DETECTION_DATE", "BG_SEVERITY"), ColumnFields:=Array("BG_PROJECT_DB", "BG_USER_01")

I want to create multiple pivottables so I created a seperate sub and intend to pass information to them. I have tried doing it as follows but it doesn't work:

Dim row_fields As String
Dim column_fields As String

Row_Fields = """BG_DETECTION_DATE"", ""BG_SEVERITY""" .....................

View 72 Replies


Method Of Range Class Failed

Aug 29, 2002

i just figured out that when you change sheets too often in VBA and get an "Activate Method Of Range Class Failed" or a "Select Method Of Range Class Failed" etc, simply retype the sheets command before it

for example :




this code in VBA, if used too often or the mentioned sheet is not the currently selected sheet in a Sub, will cause errors, and to correct this, simply type


this makes sure that the sheet is selected before running any other range/sheet type commands.

View 2 Replies View Related

Method Of Worksheet Class Failed

Aug 29, 2007

I have a worksheet "Create Origin Zones" - sheet #17 in the array - that has 56 checkboxes.

Users can click anywhere from 1 to 56 checkboxes, and for each checkbox that is checked, I unhide a sheet "Shp Profile Tmpt", copy it after sheet #17, rename it to "Origin " + checkbox#, and give it a title based on a variable in another sheet.
Once that loop is done, I then hide the "Create Origin Zones" sheet, but the user can click a button on the new sheet to go back to the 'Create Origin Zones" sheet and add more zones by clicking more checkboxes and re-running the macro.

Problem is I get that "Run-time error '1004':Copy Method of Worksheet Class failed" when I copy too many sheets. All the solutions I saw involve saving, closing and reopening the workbook but this interrupts my loop. (It would save my workbook and close it.)

I am trying to have somewhere in my loop, say every time 20 or more checkboxes are checked, its saves, closes, reopens and continues the loop to the next checkbox and repeats the copy and paste and renaming etc.

Below is my

Sub pick_origin()

chkcounter = 0

shtnum = Sheets("Create Origin Zones").Index ' find sheet# of 'Create origin zones' to copy sheets after

For i = 1 To 56 'for the 56 origin zones checkboxes

View 4 Replies View Related

'Select Method' Failure 'error 1004 Select Method Of Range Class Failed'

Oct 28, 2008

My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,

View 4 Replies View Related

Paste Method Of Worksheet Class Failed

Aug 21, 2014

I have an application I just built last week that seemed to be working fine. It's purpose is simple. It allows the user to select multiple .rtf files from a network drive, and then it loops through them, opens them up one at a time, copies the entire contents into a "temp" sheet in the excel workbook, then copies certain data from the temp tab to the next two columns in the main "data" tab of the worksheet, deletes the temp sheet, and then repeats the process for each selected .rtf file. Finally , it does some formatting of the "data" tab. All in all, it works quite well. Or at least, it DID.

Sub CopyRTFDocToTemp()
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
With WordApp

[Code] .....

As of last night, we started getting various errors.

"Run-time error '1004': Paste Method of Worksheet class failed" error messages occasionally. The frequency seems to have increased.

We were also getting an error message that reads "Microsoft Excel is waiting for another application to complete an OLE action" error messages.

Then when I stepped through the code, I was getting an error message that read [filename I'm trying to open] "is locked for editing by" [me]. "Do you want to Open a Read Only copy, Create a local copy and merge your changes later, or Receive notification when the original copy is available"

I suspected two issues
1. The macro is not successfully opening the source file before it tries to copy it, so there is nothing to paste into Excel
2. The word file was not closed the LAST time I ran the app, so the file is getting stuck open, and I have no way to manually close it.

Finally, I rebooted the PC, and added code (activedocument.close) to close the word app after copying the contents of the rtf file


Sub CopyRTFDocToTemp()
Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
With WordApp

[Code] ....

I'm considering adding a wait loop to ensure that the app has actually opened a copy of the rtf file, so it has something to copy, but I'm unsure what variable to check with that loop.

View 2 Replies View Related

Select Method Class Range Failed

Oct 31, 2007

I have created a spreadsheet that is 38mb and needed to size it down. I recorded the following macro to do this. This essentialy jut copies the top line of the relevant columns on each page and then copies the formulas down, calculates, and then pastes just the values of these calculations, thereby reducing the sheet to a third of its size. The macro works if I run it from the macro option on the toolbar, but when I assign it to a command button I keep getting ' select method

View 13 Replies View Related

VBA - Paste Method Of Range Class Failed

Dec 29, 2011

I try to copy some cells from one workbook to another but keep getting the error message "paste method o range class failed."

I tried different ways to paste, such as

range("D157").PasteSpecial Paste:=xlPasteValues
range("D157","D330).PasteSpecial Paste:=xlPasteValues

and got the same result.

If Dir(sourcePath & Format(voucherDate - 1, "dd/mm/yyyy") & ".xlsm") "" Then
Workbooks.Open Filename:=sourcePath & Format(voucherDate-1, "dd/mm/yyyy") & ".xlsm"

Range("H157", "H330").Copy

Cells(157, 4).PasteSpecial Paste:=xlPasteValues

View 7 Replies View Related

Copy Method Of Range Class Failed?

Mar 7, 2014

So below is the VBA ....

Sub quicker_Option()

Dim toDel(), i As Long
Dim RNG As Range, Cell As Long
Set RNG = Range("d2:d2500")
For Cell = 1 To RNG.Cells.Count
If Application.CountIf(RNG, RNG(Cell)) > 1 Then

[Code] .......

This is the line to debug it says

Range(toDel(i)).EntireRow.Copy ("Sheet2")

View 2 Replies View Related

AutoFilter Method Of Range Class Failed

Apr 2, 2014

Code is attempting to autofilter any rows where text does not contain "0200" as indicated below, and delete all those rows. When reaching the line of code below the macro bombs and I get a "AutoFilter method of Range class failed" error message. Why that line is not being recognized.

Const strTOFIND As String = "0200"

Dim lngLastRow As Long
Dim rngToCheck As Range

'Application.ScreenUpdating = False

With Sheets("Platform")

[Code] ........

View 2 Replies View Related

Select Method Of Worksheet Class Failed

Nov 17, 2008

I have all sheets selected:

Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible Then ws.Select (False)

' The thing is that i now want to ungroup or select the first worksheet

Sheets(1).Select ?

And then run the same sub on all the worksheets by this:

Dim wSheet As Worksheet

For Each wSheet In Worksheets

Next wSheet

Debug error is:

Select method of worksheet class failed: Sheets(1).Select ?

View 9 Replies View Related

Copy Method Of Worksheet Class Failed

Dec 15, 2008

I am getting this error when I run the following VBA script.

Sub AutoShape3_Click()
ActiveSheet.Copy After:=Sheets(14)
End Sub
The break mode is highlighting
ActiveSheet.Copy After:=Sheets(14)
as the source of the error.

I've run this code many times before with no problem. The workbook has 48 worksheets in it. It's my understanding that Excel can handle many more worksheets, so that shouldn't be a problem.

View 9 Replies View Related

Copy Method Of Range Class Failed

Jan 20, 2009

Set rngData = wksData.UsedRange

With rngData
' clear existing filter
' filter on Owner col F
.AutoFilter field:=6, Criteria1:=varOwner
' check for count > 1 since heading row should always be visible
If .Columns(1).SpecialCells(xlCellTypeVisible).count > 1 Then
.SpecialCells(xlCellTypeVisible).Copy rngOutput
End If
'clear filter
End With

Set rngOutput = wksSell.Range("A1")

It fails on the line highlighted in red, and from searches on this forum and google I think I need to specify more exactly which sheet it is. something like wksdata.SpecialCells.... but this doesn't work.

View 9 Replies View Related

Select Method Of Range Class Failed

Oct 7, 2003

When the public routine ChangeColours is called from a Command button called ButtonX on SheetX it works fine. But a command button called ButtonY on SheetY cannot run it. When ButtonY is clicked, the error is at the line :


Where it says that "Select Method of Range class failed"

Private Sub ButtonX_Click()
Call ChangeColours(0)
End Sub

Private Sub ButtonY_Click()
Call ChangeColours(0)
End Sub...............

View 9 Replies View Related

Copy Method Of Worksheet Class Failed ..

Nov 18, 2006

I've developed an administrative package in Excel for an After School Care programme. Essentially, there are 57 Child Records. Each record is stored on its own sheet. There are four other sheets in the workbook: three hidden templates, and the front page which holds a summary of all child attendance and balances.

Up until a short time ago, invoices were created from an "Invoices.xlt" file - the program would open the file as a new book, and copy the 1st Template page out until all invoices were created. This worked fine. I've recently changed this so the Invoice template is stored within the main workbook. After I did this, we started having problems.

We can run off up to 25-35 invoices fine (32 at this stage), but eventually it hits a point where the Activesheet.Copy command fails. After this point it is impossible to copy further sheets. I CAN, however, use Insert -> Worksheet. It is only the copy function that fails.

Because it's such a strange problem, I've uploaded an example with children's names changed. It can be found on [url]

The steps to recreate the problem are simple: Open the file, select all of the children's names (from Child to Child z), and click the "Create Invoices" button at the top.

View 4 Replies View Related

Pastespecial Method Of Range Class Failed

Oct 3, 2007

I have written some code to move data from one sheet to another. Since the from sheet has formulas, I use the PasteSpecial command. I have used code like this for years, and all the sudden, this starts breaking. And, here is the fun part, I run the code and it works sometimes. I never know when it will fail. It is so random. This is killing me. I have tried to create objRange object and assign them and it works sometime and fails others. Also, I tried adding the line Worksheets("Daily Dashboard"). Range ("C72").Select before the first PasteSpecial as to select the cell first before pasting. Then I get the "Select method of range class failed".
Lastly, I tried copying the code from behind a worksheet into a new module. The code is triggered by a button on the first worksheet. Still fails.

Private Sub cmdGetData_Click()
[Result1].Value = ""
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Worksheets("Calculation Sheet").Range("A39:A62").Copy
Worksheets("Daily Dashboard").Range("C72").PasteSpecial xlPasteValues
Worksheets("Calculation Sheet").Range("C39:C62").Copy
Worksheets("Daily Dashboard").Range("E72").PasteSpecial xlPasteValues

[Result1].Value = "Complete"
Application.Calculation = xlCalculationAutomatic
Sheets("Control Panel").Select
Application.ScreenUpdating = True
End Sub

View 7 Replies View Related

1004 Select Method Of Range Class Failed

Mar 10, 2007

I'm getting "1004 select method of range class failed" on the Range statement below. This code is preceded by a number of range selection and formula-setting statements, nothing unusual.


View 13 Replies View Related

Select Method Of Range Class Failed Error

Oct 22, 2008

I have a macro that opens a specified woorkbook that changes every month. There are formulas which are pasted to range I1 of the new workbook to calculate the totals on this sheet. Everytime i run the code though, I get an error that says "Select method of range class failed" and Range("I1").Select is apparently the error.

View 7 Replies View Related

Select Method Of Range Class Failed "Select Method Of Range Class Failed"

May 22, 2009

I m trying to use some simple macro recording for a command button click. I keep getting the "Select method of range class failed" error from the following code.
From reading the forum it seems to be a common mistake by newbies, couldnt work it out for myself command button is located on a different sheet to where the select function must work...from the code you can see that there are around 20 sheets that need to be used from the one command button...

Private Sub CommandButton1_Click()

View 3 Replies View Related

Select Method Of Range Class Failed Error

Jan 27, 2013

Why I'm getting the above error when I try to copy and sort data into a workbook?

I'm using this:

Sub GetData1()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:Data"

[Code] .......

To import the data and then these lines to copy the unique records to a range:

Sheet33.Range("C1:C1000").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Sheet33.Range("S1"), Unique:=True

But the above lines are highlighted when I get the error.

View 1 Replies View Related

Set Of Values - Select Method Of Range Class Failed?

Sep 6, 2013

I am trying to copy set of values in a particular row as per the condition (Value in cell (1,2) = -40). find the code below:

Sub Mohan()
Dim Row As Integer


View 6 Replies View Related

Select Method Of Range Class Failed Error?

Jul 23, 2014

Macro has been working fine for ages suddenly have received this consistently?

It's not debugging to allow me to see the route of the problem.

i've googled and seen it's normally a use of select but can't see any issue in my code or understand why it would work for months and now stop.

note: also after i click Ok on the error the macro seems to continue running and data is pulled through. ...but this message comes up every time so it's questioning the reliability of the data it's pulling.

View 4 Replies View Related

PasteSpecial Error: Method Of Range Class Failed

Apr 12, 2007

Check Personnel Number in Data Tab with Personnel Numbers in Insert Tab. If they match copy that row from Insert Tab and paste it into the next available row in the Moves Tab.

Column Descriptions: Name, Age, Phone Number, Personnel Number, Notes

Worsheet Tabs: Data, Insert, Moves ....

View 9 Replies View Related

Error :: PasteSpecial Method Of Range Class Failed

May 5, 2008

I found the following for something that I was searching for on the web.

Selection.Columns.PasteSpecial Paste:=8

My question is two fold, 1) What does the '8' mean? 2) Is there someplace that tells me what other numbers for PasteSpecial mean?

This solved a problem that I had when trying to do a PasteSpecial for Column Width. What I had been trying was the following:

Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

But I would get the error run time error '1004', PasteSpecial method of Range class failed.

View 9 Replies View Related

Paste Method Of Worksheet Class Failed - But Not On Step-thru Using F8

Jan 29, 2009

My spreadsheet has a grouped graphic that I need to repeat in a variable number of rows. I wrote a macro that copies the grouped graphic from above and pastes and positions it in the newly inserted row.

It works great when I use F8 to step thru, but if I try to actually run the macro, I get an "Paste Method of worksheet class failed." error.

Sub InsertEmployees()
Selection.EntireRow.Insert Shift:=xlDown
ActiveSheet.Shapes("Group 129").Copy
Application.CutCopyMode = False
End Sub

View 9 Replies View Related

Copy Method Of Range Class Failed 2000

Jul 8, 2006

I encounter error 1004 and copy method of range class failed in this VB routine:

fltPreviousValue = Range(varPreviousCell).Value
fltCurrentValue = Range(varCurrentCell).Value
fltNextValue = Range(varNextCell).Value


If fltCurrentValue = RangeMax(ActiveCell.Offset(-2, 0), ActiveCell.Offset(2, 0)) And _
Range(varPreviousCell).Value < Range(varCurrentCell).Value And _
Range(varCurrentCell).Value >= Range(varNextCell).Value Then
Selection.Copy (ActiveCell.Offset(0, 6))

I am using Excel 2000 and the error occured at the last command, the bolded one.

View 9 Replies View Related

Paste Method Of Worksheet Class Failed When Macro Run Twice

Sep 30, 2006

I am working on a file which is a log of all the requests we send out to our vendors. We regularly need to re-send these requests to remind them they haven't responded yet, so I am working on a macro which takes the info from the log and re-populates the request form so everything doesn't have to be re-typed every time. I've added a MsgBox as a double-check to force people to confirm they want to re-send the request.

My problem is that if you hit "No" on the MsgBox, then try to run the code again, it gives me a Run-time Error 1004 saying "Paste method of Worksheet class failed". Can someone look at my code and see if you can tell me why it works the first time but not the second?

Sub RegenerateRequest()
If ActiveCell.Column = 1 And ActiveCell.Row > 7 Then
Application.Run "LogUnprotect"
Sheets("Regenerate Request").Activate
Application.Run "RegenFormUnprotect"
ActiveSheet.Paste 'this is the line the debugger highlights.............

View 3 Replies View Related

Method Of Object Class Failed. Adding Borders

Jul 28, 2007

Error 1004 on the 5th "With Selection" Chunk, .LineStyle part.

What I did is recorded the formatting I wanted on a selected region, then changed the major references to the defined region in my code called rngATotals.

At first I got errors with the "Select" method of the "Range" class with my rngATotals line (the first one). But then I clarified public variables and now its in the formatting chunk that was recorded.

I've tried:
--Changing security/protection settings
--Clarifying my rngATotals references
--Making the sheet containing rngATotals visible (still in my code snippet)
--And not using rngATotals to define my range but more specific, clunky references like Range ("A3":"B7") or Range(Cells...,Cells...) or Range ("RAnge'sName")

What further could I clarify? And why does it have a problem with the 5th borders/linestyle reference and not the previous ones?

I hate to have to ask another question about this error because there are so many...
Is there anything beyond what I've tried that is a usual way to approach this problem?

Anyway heres the

Sub FormatTotalsTables()
' FormatTotalsTables Macro
' Macro recorded 7/22/2007 by AEB

SumSheet.Visible = True

rngATotals.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)

View 9 Replies View Related

Paste Special Method Of Range Class Failed Error?

Jan 29, 2014

This script is resulting in:

Run-time error '1004'

PasteSpecial method of Range class failed.

[Code] .....

It highlights

[Select Code] .....

when I hit debug.

View 2 Replies View Related

Delete Method Of Range Class Failed - Error 1004

Feb 9, 2012

I'm attempting to write an OutLook 2007 macro that extracts excel spreadsheets from incoming email and saves them as a .csv file. Before saving the file I need to delete the header row. I am getting a "Error 1004 - Delete method of range class failed" error when running this code.

Here is the code: (the bolded line is where I'm getting the error)

Public Sub ConvertToCsv2()
Dim xls As Excel.Application
Dim oWB As Excel.Workbook
Dim tmp As String
Dim ws As Excel.Worksheet


View 7 Replies View Related

Sort Method Of Range Class Failed Error 1004

Apr 6, 2012

My code is :
Public Const GRAPH_PutData_COL1 = "A"
Public Const GRAPH_PutData_COL2 = "B"

Range(GRAPH_PutData_COL1 & "1").Sort Key1:=Range(GRAPH_PutData_COL2 & "1"), _
Order1:=xlAscending, _

it is showing error, "sort method of range class failed error 1004"

Above error only showing when first record is empty...

View 2 Replies View Related

Copyrights 2005-15, All rights reserved