Range Method Fails When Moving List To Other Worksheet
Oct 1, 2008
I had this code working on a Range in a Worksheet. I decided I needed to move the range to another worksheet. I moved the data to column AA in a new sheet called Audit_Pool and renamed the sheet name in ThisWorkbook.Sheets, but I get a runtime error at the With Range statement.
With ThisWorkbook.Sheets("Audit_Pool").Range("AA:AA")
With Range(.Cells(1, 1), .Cells(.Rows.Count).End(xlUp))
Randomize
With .Cells(Int(.Rows.Count * Rnd()) + 1, 1).Resize(1, 2)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
what I missed when I moved my list to a different sheet?
View 9 Replies
ADVERTISEMENT
Apr 26, 2007
I'm trying to accomplish the following: on "LogSheet" sheet, click on the DailyCloseButton and have various lists on the "lists" sheet automatically sorted before I save and end. The code below gives an error: "Method ' Range' of object '_Worksheet' failed". I have no idea why it failed.
Private Sub DailyCloseButton_Click()
' DailyClose and sort routine
'Sort lists
Sheets("Lists").Activate
Range("Products").Select
Range("Products").Sort Key1:=Range("Products").Cells(2, 1), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal.............
View 9 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
Dec 26, 2006
When I call the following code from my Visual Basic 6 program to an excel spreadsheet, it runs great the first time, but then fails everytime after that unless I close vb6 and reopen it.
Private Sub lblGroundScheduleExcel_Click()
'return day of week
'MYHEADERDAY = Format(Now, "dddd")
'get day
''MyDay = Format(Now, "dd")
MyDay = Format(DTPicker1.Value, "dd")
'get month
''MyMonth = Format(Now, "mm")
MyMonth = Format(DTPicker1.Value, "mm")
'get year
''MyYear = Format(Now, "yyyy")
MyYear = Format(DTPicker1.Value, "yyyy")
View 3 Replies
View Related
Sep 19, 2007
I am using the OnTime method to automatically send out a spreadsheet by email at a certain time. I am using Windows task scheduler to start up excel and OnTime to execute the email. I have got this to work on my local drive however I need to save the spreadsheet on a network drive. Task scheduler opens this up still, however OnTime doesn't seem to be working as I have tested it and the email doesn't get sent.
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
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
Jun 11, 2008
I've the following inherited macro which basically opens a CSV text file and copies the contents to another workbook + other things.
Sub GetFile()
' GetFile Macro
Dim ws As Worksheet
Sheets("Menu").Select
' Set Path and Filename and Import
PathName = Range("E4").Value
Filename = Range("E5").Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName & Filename
' Setup Temp worksheet
TabNameTemp = "Temp"
' TabName = Range("A2").Value
TabName = "CSV File"....................
with the error: "Run-time error '1004':. Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can selct the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook." Nothing has changed in the CSV file or the Workbook with the macros.
View 2 Replies
View Related
Oct 1, 2009
I'm working on trying to maintain an inventory management sheet where i basically enter all the spare parts issued to vehicles. Now there are more than 700 parts dat form the components of a vehicle, so i decided to split this list into 12 spare groups(Gear, Engine, Body, Axle, etc etc..) and then based on wotever spare group being selected my sheet wud pull dat specific spare group list.
Since i've worked with indirect function before i thought this was a piece of cake!
However when i actually sat working on getting my sheet together i realised its not gonna be dat easy! Basically my spare group lists are a work in progress and so new items would/may be added to these lists as we start working on this sheet. So i decided to make them dynamic by using the offset function. But list validation fails when i use Indirect function on dynamic ranges!
View 2 Replies
View Related
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
May 9, 2007
I'm trying to find the last row on a sheet and then set the next cell, in column A to a certain value. It fails with Method 'Range' of object '_Worksheet' failed.
'activate the male page
sMalePicks.Activate
'add to last row of male baggage
lastRow = sMalePicks.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'get next row
lastRow = lastRow + 1
sMalePicks.Range(Cells(lastRow, 1), Cells(lastRow, 1)) = Trim(bagNames(x))
View 5 Replies
View Related
Apr 9, 2014
Each month I get a report that is formatted by the customers and this sheet never changes. The problem is that the master sheet my job uses is not formatted this way. The master workbook has several sheets that breaks down the data from the monthly sheet. Instead of having to enter the data manually I'm looking for a way to export the data from the monthly sheets into the master workbook. As I stated the sheets are all the same but come from different providers and all contain the same amount/style of data.
View 1 Replies
View Related
Oct 27, 2013
I've set up a Named Range that refers to an array of constants, e.g. MyRange ={1,2,3,4,5}. This appears to be fine since I can output the named range to a range of cells in a row (e.g. A1:E1) and see 1,2,3,4,5 (one value in each column).
I'd like to use MyRange as the source for a Data Validation list in a cell.
Normally data validation list sources must be vertical, so I've set MyRange =TRANSPOSE({1,2,3,4,5}). This appears to be fine since I can output the named range to a range of cells in a column (e.g. A1:A5) and see 1,2,3,4,5 (one value in each row).
When I set MyRange as the source for data validation however, it resolves to an error.
why this setup doesn't work?
Workbook detailing the problem attached. Attachment 273788 NamedRangeDataValidationArrayConstansts.xlsx
View 3 Replies
View Related
Aug 19, 2012
I have the code below which I need to change so it lists the file names down the column on the active worksheet starting at Range("A110").
At the moment it insterts a new worksheet and list the file names starting at A2, also I do not need "The files found in " & objFolder.Name & "are:"
what the code under 'clean up' is doing.
VB:
Sub ListAllFile()
Dim objFSO As Object
Dim objFolder As Object
[Code].....
View 3 Replies
View Related
Jan 6, 2010
I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:
View 2 Replies
View Related
Apr 26, 2006
Is it possible to move a worksheet from a workbook to an add-in.. just can't seem to do it for some reason. As a last-ditch attempt, I tried to convert the add-in to a workbook, but I can't seem to do that either
View 4 Replies
View Related
Mar 14, 2013
I have 10 worksheets. I would like to create a macro to find all the "2" values on worksheet 1, and have the actual range that the cell is, compiled into a list -example: I would like the list to be similar to this= (A1,B15 ,C8)
I even tried to record it but it doesnt show me the actual range that the data is found in.
View 9 Replies
View Related
Mar 21, 2013
I am trying to move items up and down my listbox2 I have the code below however there seems to be an error with the moving down button
Option Explicit
'Move ListBox Items code
Private Sub BTN_moveselectedLeft_Click()
[Code].....
View 1 Replies
View Related
Feb 23, 2014
Excel Move-Copy Issue.pdf
I'm simply trying to copy a worksheet (from one location within a file) and place the copy elsewhere in the file. I continue to get an error message that looks like this: "A formula or sheet you want to move or copy contains the name 'Additional_Notes', which already exists in the destination worksheet. Do you want to use this version of the name? To use the name as defined in the destination sheet, click Yes. To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box."
The term above 'Additional_Notes' is just one of MANY references which (apparently) "already exist in the destination worksheet". I tried renaming the range as described above, and when I do, the above error message essentially re-appears, but this time the term 'Additional_Notes' is replaced with another bit of text. And so on. I am not able to copy the worksheet and place it elsewhere in the file without this error message cropping up. I've used the move/copy function thousands of times.
I am able to make a copy of the worksheet and move it into a new (blank) XLS file...but NO luck copying the worksheet and placing the copy in the same file...
View 1 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
Apr 30, 2009
I have extracted a report from a web package into Excel - basically a list of documents held on our website. The Cells in Column B would have the word Title or Description, Column C would have the actual title of the document, Column D would be blank on rows with Title in Column B and Column E has a Document ID (DocID)Number. To make this easier to read is it possible to filter Column B using Title and then move the DocID number from Column E to Column D.
I have attached a shortened version of the spreadsheet with the filter applied in Column B already. The real spreadsheet is 1000 lines long and would take quite a while to drag and drop each DocID number from Column E to D. I assume this can only be done with VBA.
View 4 Replies
View Related
Nov 1, 2007
I need to identify values which are less than 0 and move those cells to an adjacent cell.
Acct Desc Amt
2100 Acct1 -10
2101 Acct2 10
The -10 cell will move one cell to the right. I have an imported list with gl accounts, descriptions and amounts and want to have the negative values moved to a new column.
View 9 Replies
View Related
Feb 19, 2009
I have in sheet1 a column "A" with random dates in this format x/x/200X.This sheet is constantly updated with new random entries as more information arrives.
So, how can I use a formula to automatically move to sheet2 "only" the rows with dates for JANUARY. I know that I can move data using e.g....=sheet1!A1, but I don't know how to use this formula with an specific condition to move the rows with JANUARY dates only.
In the attachment, you can see how I would like to move "only" the rows with JANUARY dates to sheet2.
View 11 Replies
View Related
May 22, 2008
We use work order worksheets in excel with basic information: Date in, work order #, account, date out, total # of days, work conducted by, number of samples, description of sample, etc. Each work order has an individual worksheet. The information in these worksheets eventually get entered into a work order log containing the same information in order to compile a quarterly report. We might have 400 work orders in a quarter.
My question is: How do I link the individual worksheet to the work order log in order to get the data to transfer automatically when a new work order is entered into a worksheet? I'm able to get the first work order to link to the log worksheet, but CAN NOT figure out how to get subsequent work orders to enter into the following rows, rather than just replace the first row over and over.
View 14 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
Jun 26, 2008
I have encountered what seems like a very frustrating and stupid Excel vba bug.
When I use the worksheet.delete method. My vba project is RESET.
All forms object classes and variables disapear into the BIT BUCKET.
View 9 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
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
