Worksheet Copy: Method 'Copy' Of Object 'Worksheet' Failed
Nov 22, 2006
I have written code that allows a user to copy, via a button, a certain sheet any number of times. They can choose to copy that sheet 5 times and then 10 times, etc.
When the sheet tries to be copied for the 17th time, it fails with the following message: "Run-time error '1004':
Method 'Copy' of object '_Worksheet' failed"
Here is the code that I have. The second line is the line that is failing.
Worksheets("RoedForm").Select
Worksheets("RoedForm").Copy After:=Worksheets(iCount)
This always fails on the 17th copy regardless of how many different combinations of copy sheets the user tries. There are several sheets before the sheet that is to be copied and I have deleted several of those to see what happens and it still fails on the 17th copy. I also have 1GB of memory, so I don't believe that memory is an issue.
View 3 Replies
ADVERTISEMENT
Dec 15, 2008
I am getting this error when I run the following VBA script.
Sub AutoShape3_Click()
Sheets("Template").Select
ActiveSheet.Copy After:=Sheets(14)
Duplicate.Hide
wksName.Show
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
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
Sep 5, 2006
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.
View 5 Replies
View Related
Jan 6, 2007
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.
View 3 Replies
View Related
Sep 10, 2006
Sub a()
z = 4
y = 7
sWork. Range(Cells(1 + nRow(z), 4), Cells(1 + nRow(z), 12)).Replace y, 0 lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
End Sub
but when I include it in my main code
sWork.[A1:L9] = 123456789
For z = 1 To 81
y = [A1].Offset(nRow(z), nCol(z))
If y > 0 Then
sWork.Cells(1 + nRow(z), 1).Replace y, 0, lookat:=xlPart,searchorder:=xlByRows, MatchCase:=False, matchbyte:=False
sWork.Cells(1 + nCol(z), 2).Replace y, 0, lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, matchbyte:=False
sWork.Cells(1 + nBox(z), 3).Replace y, 0, lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False, matchbyte:=False
sWork.[A1].Offset(nRow(z), nCol(z) + 3).Value = 0
sWork.Range(Cells(1 + nRow(z), 4), Cells(1 + nRow(z), 12)).Replace y, 0, lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
sWork.Range(Cells(1, 4 + nCol(z)), Cells(9, 4 + nCol(z))).Replace y, 0, lookat:=xlPart, searchorder:=xlByColumns, MatchCase:=False
sWork.Range(Cells(1 + Int(nRow(z) / 3) * 3, 4 + Int(nCol(z) / 3) * 3), Cells(3 + Int(nRow(z) / 3) * 3, 6 + Int(nCol(z) / 3) * 3)).Replace y, 0, lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False
End If
Next
I get a "Method 'Range' of object '_Worksheet' failed" error at line 9,
when z = 4, y = 7, and nRow(z) is a UDF which = 0.
View 8 Replies
View Related
Jul 6, 2007
I am having a problem with this bit of code. When I try to run it it shows Method Range of object Worksheet Failed.
The original code I adapted this from works just fine, and the only thing I have changed is the ranges and the sheet.
The code is:
Sub Filter()
With Sheet18
'
.Range("DisbData").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=.Range("Criteria"), Unique:=False
.Range("DisbData").SpecialCells(xlCellTypeVisible).Copy Destination:="DisbPaste"
.ShowAllData
End With
End Sub
The line that the debugger is showing has issues is:
.Range("DisbData").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=.Range("Criteria"), Unique:=False
View 4 Replies
View Related
May 17, 2014
I'm using Excel 2013 and I'm getting an issue in vba I can't figure out. (This is something I've done several dozen time before) But everytime I try to copy a sheet in a workbook,
Sheets("Sheet1").Copy After:=WB.Sheets(WB.Sheets.Count)
I recently copied in this sheet from another workbook, and deleted all of it's formula names, but I can't copy any other sheets now either.
The Run time Error 1004: Copy method of Worksheet Class failed pops up. What is weird is that I put in a msgbox and
MsgBox (WB.Sheets.Count)
returns a "1" though the sheet has about a dozen sheets within it. I've saved the workbook and even saved it as another name.
The sheet I imported has a sheet number of 77 while the previous last sheet was 23, could this be a cause?
View 2 Replies
View Related
Feb 15, 2007
From the current open book, I'm opening a 2nd book, then copying all worksheets from the 2nd book that meet criteria, into 1st book, (in the same order), but am getting Method 'Copy' of object '_Worksheet' failed error. What am I doing wrong?
How to OVERWRITE worksheets?We'll be running the same process with new data, so also need to overwrite worksheets in 1st book.
Sub Build_Branch_File()
Dim FileName As String
Dim Wkb As Workbook
Dim Ws As Worksheet
Dim WNum As String
Dim Tnum As String
Dim RegionNo As Integer
Dim Original_Wb As Workbook
With Application
. ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set Original_Wb = ThisWorkbook .........................
View 9 Replies
View Related
Feb 28, 2013
I M Getting Run Time Error 1004 Method Range Of Object _ Worksheet Failed
Option Explicit
Private Sub cboPart_AfterUpdate()
'On Error Resume Next
[Code]...
View 1 Replies
View Related
Dec 10, 2008
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
View 3 Replies
View Related
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
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.
VB:
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
VB:
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
Nov 17, 2008
I have all sheets selected:
Dim ws As Worksheet
For Each ws In Sheets
If ws.Visible Then ws.Select (False)
Next
' 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
Nov 13, 2008
I am running a vba code to add about 200 sheets...my code generates a sheet in one file and then pastes it in another file....after generating nad adding 38 sheets i get the error copy method of excel failed
i tried clearing the clipboard and resuming the code but that didn,t work
View 9 Replies
View Related
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()
Range("B18").Select
Selection.EntireRow.Copy
Selection.EntireRow.Insert Shift:=xlDown
ActiveSheet.Shapes("Group 129").Copy
Application.CutCopyMode = False
ActiveSheet.Paste
End Sub
View 9 Replies
View Related
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"
ActiveCell.EntireRow.Copy
Sheets("Regenerate Request").Activate
Application.Run "RegenFormUnprotect"
Range("A40").Select
ActiveSheet.Paste 'this is the line the debugger highlights.............
View 3 Replies
View Related
Jul 13, 2007
I'm getting an "Object Required" error on the following code. I can't figure out what the problem is. It copies the worksheet properly but fails to set the objWS object to the newly created sheet. I'm sure it's something simple. Set objWS = objWB.Worksheets("Product").Copy(after:=objWB.Worksheets("Product"))
View 4 Replies
View Related
Jul 31, 2007
I am trying to filter and copy from masterlist to wholesalecertified. The data is actually pasted to WholesaleCertified, however, I got error 1004.
Sub Refresh()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
Sheets("WholesaleCertified").Select
Cells.Select
Selection.ClearContents
Sheets("MasterList").Select................
View 9 Replies
View Related
Nov 26, 2008
I have a pretty simple macro that I recorded and attached to a button. The macro is:
Sub Paste_Data()
Cells.Select
Selection.ClearContents
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Range("A3").Select
End Sub
The user opens the file that contains this macro, then runs a report from a website that dumps into an excel file. They copy the data from the Book1 output, then click the button to paste it into the template. If done this way, it works fine.
However, if they run the report and get Book1 THEN open the file containing the macro, they get a run-time error 'PasteSpecial method of Worksheet class failed' on the 'ActiveSheet.PasteSpecial... line
View 9 Replies
View Related
Jun 6, 2005
Very new to VBA and having trouble with a simple macro running in 2003 that copies and pasts to another worksheet. Code as follows:
Private Sub CopyResults_Click()
'Select the filtered data and copy it
Range("A23").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F19").Select
' Open the template and copy in the data
Workbooks.Open Filename:="C:Athens Verification DataTemplatesVerification Template.xls"
Workbooks("Verification Template.xls").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Range("A1").Select
Application.CutCopyMode = False
End Sub
When I run CopyResults, the sheet is opened and the data copied correctly, but I get a runtime error 1004 past method of worksheet class failed, with the line 'ActiveSheet.Paste' highlighted. Don't get this when I run the same macro in 2000.
View 14 Replies
View Related
Sep 17, 2012
I am getting this error when trying to generate the link report ....
Debug tells me that this makes reference to a sheet is not there , I have checked and it seems to be present
It is an urgent issue as it is preventing me to generate link reports...
View 9 Replies
View Related
Apr 29, 2009
I have the following macro who now return a runtime error 1004, paste method of worksheet class failed.
I don't know how to change it to make it work.
Sub Paste_TOP()..
View 9 Replies
View Related
Dec 28, 2009
i am getting a run time error 1004 Delete method of worksheet class failed,
ws.Delete
Sub Save()
Dim myName As String, myFolder As String, e
Dim fso As Object, temp As String
ThisWorkbook.Save
Set fso = CreateObject("Scripting.FileSystemObject")
myFolder = "C:UsersRecsDocumentsTestVBA & ExcelEmail_Files_Temp" & Year(Date) & "" & Format$(Date, "mmm")
For Each e In Split(myFolder, "")
temp = temp & IIf(temp = "", "", "") & e
If fso.FolderExists(temp) = False Then fso.CreateFolder (temp)
View 9 Replies
View Related
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
Jan 20, 2009
Set rngData = wksData.UsedRange
With rngData
' clear existing filter
.AutoFilter
' 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
.AutoFilter
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
Aug 14, 2014
I am copying a WorkSheet and I want to set it directly to an Object. I don't want to use the activeWorksheet. I does work like this with .add so my idea was it should work with copy too.
[Code] .....
It does Copy the Worksheet, but afterwards VBA gets error 424
SO how do I set a Workbook.copy ?
View 2 Replies
View Related
Mar 12, 2008
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:
- Worksheet Copy: Method 'Copy' of Object 'Worksheet' Failed
- Copy Method of Worksheet Class Failed in Loop Code
In my case, after running this code 40 times, it fails.
If loc_after Is Nothing Then
.COPY after:=Worksheets(sht_name_orig) 'throws error here after being called 40 times
Else
.COPY after:=loc_after
End If
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.
View 7 Replies
View Related
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
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
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
Oct 21, 2012
How to accomplish the goal described below.
A , Method of Range Object "_Global" Failed , error occurred upon running the code below at the emboldened statement.
Code:
Sub copytosheet2()
Dim Lastrow As Long, CopyRng As Range, DestRng As Variant, i&
Worksheets("Sheet1").Activate
Lastrow = Worksheets("Sheet1").[A65536].End(xlUp).Row
'
For i = Lastrow To 2 Step -1
[Code] ......
The goal is to loop through columns of data on sheet1 and copy values of cells B-C to the lowest rows on sheet2 column B-C.
Here are the screen shots of sheet1 and sheet2.
View 3 Replies
View Related
