Free Microsoft Excel 2013 Quick Reference

Verb method of OLEObject class failed - Excel97

Hi all,

I have this excel worksheet in which I have included a file using the menu Insert // Object... // create from file.

On my sheet I have this button that execute the following code to launched the file:


	VB:
	
ActiveSheet.Shapes("Object 1").Select 
Selection.Verb Verb:=xlPrimary 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I use Excel 2002, everything works fine, the file is launched from Excel. My problem is with Excel 97 where I get this error:

"Run-time error '1004'"
"Verb method of OLEobject class failed"

Did anyone already have this problem and how can I solved it.

Thanks in advance.

Fred


Post your answer or comment

comments powered by Disqus
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 code:

	VB:
	
 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
         
        chknum = "CheckBox" + Trim((Str(i))) 'variable used for CheckBox #s
        title = Sheets("Create Origin Zones").Range("O" + Trim((Str(i)))) 'variable used for range of description (cell
O1-O56)
        orgnum = "Origin " + Trim((Str(i))) 'variable used for renaming sheets
         
        If Sheets("Create Origin Zones").OLEObjects(chknum).Object.Value = True Then 'if checked
             
            chkcounter = chkcounter + 1 
            Sheets("Shp Profile Tmpt").Visible = True 'make appear
            Sheets("Shp Profile Tmpt").Select 'give focus
             
            Err.Clear 
            On Error Resume Next 
             
            Sheets("Shp Profile Tmpt").Copy after:=Sheets(shtnum) 'copy after 17th sheet
             
            On Error Goto 0 
             
            shtnum = shtnum + 1 'add to counter to add to right of next sheet
            Sheets("Shp Profile Tmpt (2)").Select 
            Range("H1").Value = orgnum + " - " + title 'insert title
            Range("Z1").Value = i 'insert origin number
            Range("H1").Select 
             '***Since we're naming the sheets here, deal with the dupes here***
            Err.Clear 
            On Error Resume Next 
             
            Sheets("Shp Profile Tmpt (2)").Name = orgnum 'rename sheet
             
             
            If Err  0 Then 
                Application.DisplayAlerts = False 
                Sheets("Shp Profile Tmpt (2)").Delete 
                Application.DisplayAlerts = True 
            Else 
                Sheets("Shp Profile Tmpt (2)").Name = orgnum 'rename sheet
            End If 
            On Error Goto 0 
             
            Sheets("Shp Profile Tmpt").Visible = False 'hide original sheet
             
             
        End If 
        Sheets("Shp Profile Tmpt").Visible = False 
    Next 
     
    Sheets("Create Origin Zones").Select 
     
    If chkcounter = 0 Then 
        MsgBox "Please check off at least one origin zone", , "Missing Information" 
        Range("d26").Select 
        Exit Sub 
    Else 
        MsgBox "Your origin zones have been created." _ 
        & Chr(13) & "Please fill in shipment information for each origin zone created," _ 
        & Chr(13) & "as well as surcharge and product discounts", , "Information" 
        If Sheets(shtnum + 1).Name = "Surcharges" Then 
            Sheets("Create Origin Zones").Visible = True 
            Sheets(shtnum).Select 
        Else 
            Sheets("Create Origin Zones").Visible = False 
            Sheets(shtnum + 1).Select 
        End If 
    End If 
     
    For cb = 1 To 56 'for the 56 origin zones
        chknumm = "CheckBox" + Trim((Str(cb))) 'variable used for CheckBox #s
        Sheets("Create Origin Zones").OLEObjects(chknumm).Object.Value = False 
    Next 
    Application.EnableEvents = True 
    Sheets("Create Origin Zones").OLEObjects("OptionButton2").Object.Value = True 
    Exit Sub 
     
    Sheets("Surcharges").Visible = True 
    Sheets("Express 9AM").Visible = True 
    Sheets("Express 1030AM").Visible = True 
    Sheets("Express").Visible = True 
    Sheets("Ground").Visible = True 
    Sheets("Express_US").Visible = True 
    Sheets("Ground_US").Visible = True 
    Sheets("Express_International").Visible = True 
     
End Sub 

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


How are you triggering the code - if with a commandbutton, try changing the
takefocusonclick property to false - just a guess of course.

--
Regards,
Tom Ogilvy

"Jack" > wrote in message
...
> Hi,
>
> I copy and paste from a worksheet to another. It works
> well on excel2000 but not on excel97-->runtime
> error '1004' delete Method of Range Class Failed. Does it
> means the versions are not compatible?.Do you know what is
> wrong?!
>
> this is what the button has:
>
> Dim ExecComments As Worksheet
> Set ExecComments = Sheets("Comments")
>
> Dim Answers As Worksheet
> Set Answers = Sheets("Sheet1")
>
> 'delete column
> ExecComments.Columns(1).EntireColumn.Delete
>
> request = InputBox("Please enter the question number to
> see the comments (1-4)", "", "")
>
> ' Find the last row of data
> FinalRow = Answers.Range("A65536").End(xlUp).Row
>
>
> ' Loop through each row
> For X = 1 To FinalRow
>
> thisValue = Answers.Range("F" & X).Value
> If thisValue = "1" Then
> thisValueComments = Answers.Range("Z" &
> X).Value
> If Not thisValueComments = "[NULL]" Then
> Answers.Range("Z" & X).Copy
>
> NextRow = NextRow + 2
> If flag = 1 Then
> ExecComments.Range("A12").Value = "QUESTION
> 1. what team you have? "
> flag = 0
> End If
>
> ExecComments.Range("A" & NextRow).Value =
> thisValueComments
>
> End If
> End If
> Next X
>
> Thanks for your help!!!
> Jack

Hi Forum

1. I implemented a worksheet with several Text Boxes from Controls Toolbox.

2. To navigate around the Text Boxes , used a Bob Phillips recommendation (thanks again Bob) that worked very well.

Re: How
to jump between TextBoxes 

--------------------------------------------------------------------------------

Here is a way

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim bBackwards As Boolean


Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
'Excel 97 must select cell before activating another control.
If Application.Version < 9 Then Sheet1.Range("A1").Select
If bBackwards Then
TextBox3.Activate
Else
TextBox2.Activate
End If
Application.ScreenUpdating = True
End Select
End Sub
3. My problems began when i "shared" the worksheet, everything works well except when i tried to navigate using the enter, tab or arrows for which i coded.
In that cases i receive the "run time error 1004" Activate method of OLEObject class failed.

4. Could not see where the trouble araises because the "shared feature" that prevents me from seeing the VBA code.

Could you give some directions or strategies on how to proceed to avoid this error.

As allways thanks for your advice.

Regards

Jose Luis

I am using the code below to delete rows based on a value from an inputbox. When I type a name in the inputbox, the rows containing that name should be deleted and then the rest of the macro is supposed to return the sheet to its original format minus the deleted rows. However, the rows are not deleted and I get an "AutoFill method of range class failed" error on the Selection.Autofill..... line. I have attached the file with the macro included. I would appreciate any help to make this work.


	VB:
	
 DisqualifySchool() 
     
    Dim Msg, Style, Title 
    Msg = "You have chosen to disqualify a school.  If you proceed, all the runners from the school will be removed and all
the points for this race will be deleted. " & _ 
    "Do you want to disqualify a school from this race?" 
    Style = vbYesNo + vbCritical + vbDefaultButton2 
    Title = "Delete All Macros" 
    Response = MsgBox(Msg, Style, Title) 
    If Response = vbNo Then Exit Sub 
     
    Application.ScreenUpdating = False 
    Application.Cursor = xlWait 
     
    ActiveSheet.Unprotect Password:="iwbi48crci" 
     
    schoolname = InputBox("Enter the name of the school to be disqualified from this race.", "Delete School") 
    If schoolname = "" Then 
        Application.Cursor = xlDefault 
        MsgBox ("A school name was not entered.  If you wish to disqualify a school, please try again.") 
        ActiveSheet.protect Password:="iwbi48crci" 
        Exit Sub 
    End If 
     
    Dim bottomC As Long 
    bottomC = Range("c" & Rows.count).End(xlUp).Row 
    Dim x As Variant 
    x = Application.Match(schoolname, Range("c4:c" & bottomC), 0) 
    If IsError(x) Then 
        Application.Cursor = xlDefault 
        MsgBox ("The school you entered was not found. Make sure that your enter the name exactly as it appears in Column C,
including punctuation and spacing.  Please try again.") 
        ActiveSheet.protect Password:="iwbi48crci" 
        Exit Sub 
    End If 
     
    Application.Cursor = xlWait 
    Dim i As Integer 
    For i = bottomC To 4 Step -1 
        If Range("C" & i) = schoolname Then 
            Application.ScreenUpdating = True 
            Application.Cursor = xlDefault 
            Rows(i).Delete 
            Application.ScreenUpdating = False 
            Application.Cursor = xlWait 
        End If 
    Next i 
     
    If Not schoolname = "" Then MsgBox (schoolname & " has been disqualified from this race.") 
     
    Dim bottomA As Integer 
    bottomA = Range("a" & Rows.count).End(xlUp).Row 
    Dim bottomE As Integer 
    bottomE = Range("e" & Rows.count).End(xlUp).Row 
     
    Range("A" & bottomE & ":E" & bottomE).Select 
    Selection.AutoFill Destination:=Range("A" & bottomE & ":E123"), Type:=xlFillDefault 
    Range("A" & bottomE & ":E123").Select 
     
    Dim bottomD As Integer 
    bottomD = Range("d" & Rows.count).End(xlUp).Row 
     
    Dim y As Integer 
    y = 1 
    For i = 4 To bottomD 
        Cells(i, 4).value = y 
        y = y + 1 
    Next i 
     
    Dim z As Integer 
    z = 1 
    For i = 63 To 4 Step -1 
        Cells(i, 5).value = z 
        z = z + 1 
    Next i 
     
    Range("E65:E123") = 0 
     
    Range("A" & bottomA).Offset(1, 0).Select 
     
    Application.ScreenUpdating = True 
    Application.Cursor = xlDefault 
     
End Sub 

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


I have a large worksheet ("AllProjects") that contains a comma delimited list of authors in column J. I need to copy the autofiltered data to another worksheet for separation into one line per author. The code seems to work fine (most of the time), but I am getting an error: "Select method of Range class failed" (it's #1004, in case that helps). The code I have at the moment is:


	VB:
	
 Expand_Data() 
     '    Application.ScreenUpdating = False
        Worksheets("AuthorAnalysis").Cells.Clear 
        On Error Goto ErrorHandler 
        Worksheets("AllProjects").Select 
        With ActiveSheet 
            .AutoFilterMode = False 
            .Range("Database").AutoFilter 
            .UsedRange.AutoFilter Field:=10, Criteria1:="", Operator:=xlAnd, Criteria2:="Author selection pending*" 
            Worksheets("AllProjects").AutoFilter.Range.Copy Worksheets("AuthorAnalysis").Range("A2") 
        End With 
        Worksheets("AuthorAnalysis").Cells(2, 1).Select 
        Call movecolJ 
        Call CountCommas 
        Call TrimCellsInColA 
     '    Application.ScreenUpdating=True
       Call msgbox1 
    ErrorHandler:
    Debug.Print Err.Number & ": " & Err.Description     Resume Next End Sub
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am at a bit of a loss to know how to identify the cause of the occasional failure, and then eliminating that. All help appreciated!

I have a large worksheet ("AllProjects") that contains a comma delimited list of authors in column J. I need to copy the autofiltered data to another worksheet for separation into one line per author. The code seems to work fine (most of the time), but I am getting an error: "Select method of Range class failed" (it's #1004, in case that helps). The code I have at the moment is:

	VB:
	
 Expand_Data() 
     '    Application.ScreenUpdating = False
    Worksheets("AuthorAnalysis").Cells.Clear 
    On Error Goto ErrorHandler 
    Worksheets("AllProjects").Select 
    With ActiveSheet 
        .AutoFilterMode = False 
        .Range("Database").AutoFilter 
        .UsedRange.AutoFilter Field:=10, Criteria1:="", Operator:=xlAnd, Criteria2:="Author selection pending*" 
        Worksheets("AllProjects").AutoFilter.Range.Copy Worksheets("AuthorAnalysis").Range("A2") 
    End With 
    Worksheets("AuthorAnalysis").Cells(2, 1).Select 
    Call movecolJ 
    Call CountCommas 
    Call TrimCellsInColA 
     '    Application.ScreenUpdating=True
    Call msgbox1 
ErrorHandler: 
    Debug.Print Err.Number & ": " & Err.Description 
    Resume Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am at a bit of a loss to know how to identify the cause of the occasional failure, and then eliminating that. All help appreciated!

I'm a macro newbie....so please forgive me if this question is really elementary!

I created a pivot table while recording it in a macro. When I tried to run the macro after I created it, I get a run time error '1004' that says AddFields method of PivotTable class failed. When it brings up the pivot table worksheet, not all of the fields were showing. Is it possible that the macro runs too fast? The first data field that is to be used in the pivot table isn't listed, which is why I wonder if it's running too quick. If so, is there anything I can to do slow it down? Appreciate any suggestions!!

I am creating a macro in excel which creates Pivot Tables When I try to run the macro I am getting a Run-time error '1004' with the message AddFields method of PivotTable class failed.
The contents of the macro are as follows, I have highlighted the line that the macro fails on.


	VB:
	
 Macro1() 
     
     '
     
     ' Macro1 Macro
     
     ' Macro recorded 05/11/2003 by @CSDMS6
     
     '
     
     ' Keyboard Shortcut: Ctrl+a
     
     '
     
    Cells.Select 
     
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
     
    "ALLITEMS!C1:C8").CreatePivotTable TableDestination:="", TableName:= _ 
     
    "PivotTable1" 
     
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
     
    ActiveSheet.Cells(3, 1).Select 
     
    ActiveSheet.PivotTables("PivotTable1").SmallGrid = False 
     
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _ 
     
    "BranchNumber", "ReferralStatus") 
     
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PackageReference"). _ 
     
    Orientation = xlDataField 
     
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel 
     
    ActiveSheet.PivotTables("PivotTable1").Format xlReport6 
     
    Application.CommandBars("PivotTable").Visible = False 
     
    Range("A1").Select 
     
End Sub 

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


Any help much appreciated

Moira

Hi,

I'm trying to copy data from one workbook to another. I am receiving run-time error '1004' PasteSpecial method of Range class failed.

I can not figure out why I am receiving this error.

Code is:


	VB:
	
 dlsheet = appexcel.Workbooks.Open(strPath & "data.xls") 
 
With dlsheet 
    .Sheets("data").Range("A1:H500").Copy 
End With 
 
 'Location of template excel file
Set tempsheet = appexcel.Workbooks.Open(strPath & "Template.xls") 
 
 'Pastes data into template
With tempsheet 
    .Sheets("data").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be gratefully appreciated. I'm sure this is a stupid error but can't fix it.

I have a piece of code that is failing only on one sheet of one workbook, but works fine on all the other sheets of all other workbooks I've tested it on. (I have 6 workbooks for testing, each with 3 sheets. All the data is in the same format on each sheet.) The code is designed to copy the Vendor ID to a blank column then split it with text-to-columns. All columns after the column the data is pasted to (J) are empty.

On this one sheet I get an error of "Run-time error '1004': TextToColumns method of Range class failed". Other sheets with this vendor in other workbooks work fine. I've tried copying the formating and columns after J from worksheets where the code ran successfully in case there was something I wasn't seeing, but those changed didn't help. I just checked, and the sheet in question is not protected.

Does anyone have any ideas on what is going wrong?


	VB:
	
Range("G:G").Select ' Selects Vendor ID column
Selection.Copy 
Range("J:J").Select ' Copies Vendor ID to J in order to split out region code
ActiveSheet.Paste 
Columns("J:J").Select 
Application.CutCopyMode = False 
Application.DisplayAlerts = False 
Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _ 
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ 
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ 
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True 
Application.DisplayAlerts = True 

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


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.

PLEASE HELP!!! Thanks in Advance!

Here is my code:

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

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


I get the following error:
Run-time error '1004': Select method of Range class failed
at the following code line:

	VB:
	
Worksheets("Sheet1").Range("A6").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Why? What is wrong with Excel?

I'm getting and error that states "select method of option class failed." Here is what I'm doing. For each row of data in the worksheet I'm putting an option button, so that when the user clicks it it will give more detail for that particular row. It gets to about row 170 then bombs out with the error above.

Maybe there a limit on the number of optionbuttons that excel can hold?
Any ideas, code below:


	VB:
	
 'call AddOptionButton (activesheet,range("C3:C500"))
 
Public Sub AddOptionButton2(shtName As Worksheet, destRange As Range) 
     
     'On Error Resume Next
    Dim c As Range, myRange As Range 
    Dim lastRow As Long 
    Dim x As Integer 
    Dim y As Integer 
     
     
    shtName.Visible = True 
    shtName.Select 
     'Debug.Print "Destrange--" & destRange.Address
     
    destRange.Select 
    Set myRange = Selection 
     
    For Each c In myRange.Cells 
        Range(c.Address).Select 
         'puts x & y pos so that it is centered within cell
        x = c.Left + ((c.Width / 2) - 5) 
        y = c.Top + ((c.Height / 2) - 6) - 2 
        ActiveSheet.OptionButtons.Add(297.75, 192, 72, 72).Select 
        ActiveSheet.OptionButtons.Add(x, y, 18, 12).Select 
        With Selection 
            .Name = Trim(shtName.Name) & "opt" & c.Row 
            .Display3DShading = True 
            .Characters.text = "" 
            .OnAction = "ManualMatch" 
        End With 
    Next 
     
End Sub 

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


I have built a simple search tool. Users input text data into a few controls one one page and press a button, then the code activates the sheet full of data and brings up a dialog box with their results. Once the dialog box is in front of them, clicking on a result in the dialog box selects the appropriate row on the worksheet (since the text in the box is truncated). Once they're done they can close the dialog and work the data directly. It was working perfectly, but after being placed on the intranet to be accessed the code that selects the line in the worksheet is no longer working properly.

After finding some information, I have updated the code a bit (now I'm trying to select a range using cells(), rather than trying to use EntireRow. The code will now run, but only once. A user can select a line once, but if they try to click on another line this error occurs:

Runtime error '1004':
Activate method of Range class failed.

Here is the code than runs when the dialog box is clicked:


	VB:
	
 lstResults_Click() 
    Dim xlBook As Excel.Workbook 
    Dim xlSheeet As Excel.Worksheet 
     
    Set xlBook = ThisWorkbook 
    Set xlSheet = xlBook.Worksheets("Schedule") 
     
    If (lstResults.ListIndex  -1) Then 
        iRow = lstResults.List(lstResults.ListIndex, 0) 
        xlSheet.Activate 
        xlSheet.Range(xlSheet.Cells(iRow, 1), xlSheet(Cells(iRow, 8)).Activate 
    End If 
     
    Set xlSheet = Nothing 
    Set xlBook = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'd appreciate any suggestions you might have.

Jeff Plummer

I am getting the following error:

Run-time Error '1004':
Copy method of Worksheet class failed

after adding 53 worksheets to a workbook using VBA. I found several posts concerning this error in the forum. However, I did not find any responses that address the root cause or provide a solution.

Does anyone have any ideas on why the class is failing?

Hi -
I've been having a problem in my worksheet where I have the following code:


	VB:
	
Sheets(JobSheetName).Range(FirstColumn & DeleteRowNumber & ":" & LastColumn & DeleteRowNumber).Delete shift:=xlUp 
Sheets(JobSheetName).Range(FirstColumn & CopyBlankRowNumber & ":" & LastColumn & CopyBlankRowNumber).Copy 
Sheets(JobSheetName).Range(FirstColumn & CopyBlankRowNumber & ":" & LastColumn & CopyBlankRowNumber).Insert shift:=xlDown 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get the error "Run-time error '1004'" Insert method of Range class failed" on the last line.

The devil of it is, this code worked earlier. I did some changes to the sheet, but nothing that should have interfered with the insert.

All I'm trying to do is keep the total table the same height - (I won't get into why now), so when I delete a row, I just want to copy and insert a specific row to keep the same height.

Can anyone offer any insight?

Thanks muchly (as always) for your time.
Ben.

Hi Everybody--
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?


	VB:
	
 RegenerateRequest() 
     ' finds active row and copies info into Re-request form to be sent to factory
     
    If ActiveCell.Column = 1 And ActiveCell.Row > 7 Then 
        Application.Run "LogUnprotect" 
        ActiveCell.EntireRow.Copy 
        Sheets("Regenerate Request").Activate 
        Application.Run "RegenFormUnprotect" 
        Range("A40").Select 
        ActiveSheet.Paste 'this is the line the debugger highlights
         
         ' This sets all of the formulas in the Re-request form to populate the
         ' data copied from the log
        Range("C12").Formula = "=D40" 
        Range("C14").Formula = "=E40" 
        Range("C16").Formula = "=F40" 
        Range("C18").Formula = "=G40" 
        Range("C20").Formula = "=K40" 
        Range("C22").Formula = "=H40" 
        Range("C24").Formula = "=J40" 
        Range("G5").Formula = "=G10+.01" 
        Range("G10").Formula = "=A40" 
        Range("G12").Formula = "=B40" 
        Range("G14").Formula = "=C40" 
        Range("G18").Formula = "=I40" 
         
         ' message box gives user option to proceed with or cancel
         ' re-generation request
         
        Dim cMsg, cStyle, cTitle 
        cMsg = "Are you sure you would like to regenerate this request?" & Chr(13) & "Clicking Yes will post this request to
the log." & Chr(13) & "Clicking No will return you to the log." 
        cStyle = vbYesNo 
        cTitle = "Confirm Regenerate Request" 
         
        Resp = MsgBox(cMsg, cStyle, cTitle) 
        Select Case Resp 
        Case Is = vbYes 
            Application.Run "YesRegen" 
        Case Is = vbNo 
            Rows("40:40").ClearContents 
            Application.Run "RegenFormProtect" 
            Sheets("Request Log").Select 
            Application.Run "LogProtect" 
            Range("A6").Select 
        End Select 
    Else: Resp = MsgBox(Prompt:="Please select a cell in the Request # column to regenerate", Buttons:=vbOKOnly) 
    End If 
    Application.Run "LogProtect" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance for any help!!

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


	VB:
	
fltPreviousValue = Range(varPreviousCell).Value 
fltCurrentValue = Range(varCurrentCell).Value 
fltNextValue = Range(varNextCell).Value 
 
Range(varCurrentCell).Activate 
 
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 
    Range(varCurrentCell).Select 
    [B]Selection.Copy (ActiveCell.Offset(0, 6))[/B] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am using Excel 2000 and the error occured at the last command, the bolded one.

Any suggestion?

Regards

Jim

Hi all

With reference set for Microsoft Scripting Runtime
U2U me if you would like to see the wb
Stripped and zipped is 78k.
=====================================

Code is raising this error:
Run-time error '1004':
Select method of Range class failed Debug comes here:

	VB:
	
.UsedRange.Select 

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

	VB:
	
 lsr_TrimALL() 
     'Original by David McRitchie
     'Adpated  by marc 01.22.2006
    Dim wbBook As Workbook 
    Dim wsData As Worksheet 
    Dim Cell As Range 
     
     'Environment
    With Application 
        .ScreenUpdating = False 
        .Calculation = xlCalculationManual 
        .DisplayAlerts = False 
    End With 
     
     
     'Objects
    Set wbBook = ThisWorkbook 
    Set wsData = wbBook.Worksheets("Data") 
     
     'Processes
    With wsData 
        .UsedRange.Select 
    End With 
     
    With Selection 
         'Also Treat CHR 0160, as a space (CHR 032)
        .Replace What:=Chr(160), Replacement:=Chr(32), _ 
        Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 
    End With 
     
     'Trim in Excel removes extra internal spaces, VBA does not
    On Error Resume Next 'in case no text cells in selection
    For Each Cell In Intersect(Selection, _ 
        Selection.SpecialCells(xlConstants, xlTextValues)) 
        Cell.Value = Application.Trim(Cell.Value) 
    Next Cell 
    On Error Goto 0 
     
     'Cleanup
     
    Set wbBook = Nothing 
    Set wsData = Nothing 
     
    With Application 
        .Calculation = xlCalculationAutomatic 
        .ScreenUpdating = True 
        .DisplayAlerts = True 
    End With 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Though I suspect may be something else.
I have calling procedure with a couple of different wb's open when the TRIMALL proc. is called.

Maybe the sht I need to run trimall on is not the active wb/ws?
Calling proc. below
Thanks
-marc


	VB:
	
 
 
Sub lsr_BatchProcess() 
     
    Dim wbBook As Workbook 
    Dim wsData As Worksheet 
    Dim FSO As Object 
    Dim fldr As Object 
    Dim Folder As Object 
    Dim file As Object 
    Dim Files As Object 
    Dim oWb As Workbook 
    Dim sFolder As String 
     
    Set FSO = CreateObject("Scripting.FileSystemObject") 
    Set wbBook = ThisWorkbook 
    Set wsData = wbBook.Worksheets("Data") 
     
     '//Environment
    With Application 
        .ScreenUpdating = False 
        .Calculation = xlCalculationManual 
        .DisplayAlerts = False 
    End With 
     
    sFolder = "C:lsr_reports_Mid" 
     
     'Process=========================================================
     
     'Open each file workbook
    If sFolder  "" Then 
        Set Folder = FSO.GetFolder(sFolder) 
        Set Files = Folder.Files 
        For Each file In Files 
            If file.Type = "Microsoft Excel Worksheet" Then 
                Set oWb = Workbooks.Open(Filename:=file.Path) 
                 
                oWb.Worksheets("Sheet1").UsedRange.Copy 
                wsData.Range("A1").PasteSpecial xlPasteValues 
                 
                 '********************************************
                 'Subroutines
                lsr_TrimALL 
                lsr_CopyCodeFormula 
                lsr_DeleteRowOnValue 
                lsr_DeleteRowOnText 
                lsr_CopyToClean 
                lsr_SummaryData 
                lsr_WriteToExistingFile 
                lsr_Cleanup 
                 '********************************************
                 
                oWb.Close 
            End If 
        Next file 
    End If 
End Sub 

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


Hello Everyone,

First I would like to thank anyone in advance who is willing to tackle this problem with me.

New guy here. I've been working on this Macro that splits up my data from a master sheet and splits it into many different tabs and names them according to the account number which is in the far most right coloumn. It groups all of the specific accounts activity in the one tab.

The problem I have is after I copy about 15 sheets or so it brings up this error:

Excel cannot complete this taks with available resources. Choose less data
or close other applications.

I push OK

then it says:

Run-Time error '1004':

PasteSpecial method of Range class failed

I push Debug

it highlights

mySht.Range("A1").PasteSpecial xlPasteValues

If i push End

it says:

The picture is too large and will be truncated.

I push OK

and it comes up two more times and the book closes.

vba code

Option Explicit

Private Declare Function OpenClipboard Lib "user32" _
(ByVal hwnd As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

Sub ExportDatabaseToSeparateFiles()
'Export is based on the value in the desired column

Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim myShtName As String
Dim KeyCol As Integer

myShtName = ActiveSheet.Name
KeyCol = InputBox("What column # within database to use as key?")

Set myArea = ActiveCell.CurrentRegion.Columns(KeyCol).Offset(1, 0).Cells

Set myArea = myArea.Resize(myArea.Rows.Count - 1, 1)

For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add(Before:=Worksheets(1))
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=myCell.Value
myCell.Parent.Cells.SpecialCells(xlCellTypeVisible).Copy
mySht.Range("A1").PasteSpecial xlPasteValues
mySht.Range("A1").PasteSpecial xlPasteFormats
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
ClearCipboard
Application.CutCopyMode = False

End With
Resume
SheetExists:
Next myCell

End Sub

Sub ClearClipboard()
OpenClipboard Application.hwnd
EmptyClipboard
CloseClipboard
End Sub

end vba

Thanks so much for your help...

Dejan

i am trying to get vba to copy a sheet with an incremented tab e.g. (sheet to be copied) "Pole" is then copied beside it as "Pole 1" and so on up to a maximum of and integer placed in a cell on the "summary" tan sheet. I have developed a sub routine that not only does this but also copies the contents of this sheet "Pole" into the next sheet and so on formula an all. These cells are then back referenced to the summary sheet with another bit of code in the vba.
The problem i am experiencing is that the program works ok up to 49 or 50 sheets but then will fail with "run time error 1004" and "copy method of worksheet class failed"
As the code seems ok up to the 49 sheet point i feel that the code works ok but there must be some limit set somewhere that is stopping the routine. I thought the only limitation of sheets is that of your RAM, which i have a gig, so would greatly appreciate help.
As i am new to this forem i am not sure how to email the code but if you get back to me i could send you my program and a doc. telling you how it is supposed to work.
Regards,
Andrew

Hello,

Im trying to use a combo box to filter data on another sheet this is what I have and it gives me the Run-time error '1004: AutoFilter method of Range class failed error.

	VB:
	
 ComboBox1_Change() 
    Application.ScreenUpdating = False 
    If Sheets("Sheet3").AutoFilterMode = True Then Sheets("Sheet3").Range("d1:d6000").End(xlUp).AutoFilter 
    Sheets("Sheet3").Range("d1:d6000").End(xlUp).AutoFilter Field:=4, Criteria1:=ComboBox1.Value, visibledropdown:=False 
    Application.ScreenUpdating = True 
     
End Sub 

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

Hi.
I have an excel sheet being used as a mini database table. Rows = records, columns = fields.

I have some VBA to create a copy of base template in the workbook, then populate the new template with the data from a row/record in the db.

I currently have about 100 records. After about the 57th record I recieve RT error 1004. "Copy method of worksheet class failed". I think this is becuase excel is running out of memory. My laptop has 1gig of ram, and i have closed all other apps when running the macro.

Is there a way to free up memory while the vba is running, without clearing my "for" or count position which tells the macro to create a new sheet and which row/record in the db to populate the data in the new sheet.

I would appreciate any advice or direction anyone could provide.
Thanks & Regards
Steve

I am encountering a problem with worksheets.copy that occurs after the copy method is called a certain amount of times. My issue is similar (exactly the same?) to what is described in these threads:

- http://www.ozgrid.com/forum/showthread.php?t=60645
- Copy Method of Worksheet Class Failed in Loop Code

In my case, after running this code 40 times, it fails.


	VB:
	
 
    .COPY after:=Worksheets(sht_name_orig) 'throws error here after being called 40 times
Else 
    .COPY after:=loc_after 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I would like this code to be able to run more than 40 times.

I have read Dave's suggested solution regarding creating a worksheet template:
http://www.ozgrid.com/Excel/excel-wo...-templates.htm

However, in my situation the client has specifically asked that the report come as a single stand alone excel file with no add ins. In addition, I am copying several different worksheets, not just one single template that is being used over and over.

Any ideas/help would be appreciated.

Thanks!


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