Limitations On The Sheet.Copy Method
Jan 28, 2007
I have a macro that copys a worksheet a user-inputted number of times but throws an error after adding Sheet #62. The line it hangs on is:
Sheets(myTemplate).Copy After:=Sheets(i - 1)
Does anyone know if this is a problem with my code, a memory issue, or the workbook I'm using to copy? In the past when I've copied a lot of sheets in a workbook (to the same workbook), Excel sometimes won't let me add anymore but if I copy all the sheets to a new workbook it will sometimes let me add more sheets. I figured it was a problem with how Excel counts sheets.
Sub CopySheets()
myTemplate = ActiveSheet.Name
'First, jump through the validation hoops
Dim AddSheetQuestion As Variant
'Define the application input box question
showAddSheetQuestion:
AddSheetQuestion = Application.InputBox("Please enter the number of sheets you want to add," & vbCrLf & _
"or click the Cancel button to cancel the addition:", _
"How many sheets do you want to add?")
'Cancel or the X was clicked
If AddSheetQuestion = False Then..................................
View 9 Replies
ADVERTISEMENT
Nov 25, 2009
I'm currently using a Lookup(2,1) that looks up an unopened excel sheet stored locally.
When I enter the formula I always get a #NA.
What I was wondering was if its a limitation of the lookup funcation, or my PC not having enough resources.
The linked XL sheet is ~10mb, and has over 8000 rows.
Below is my current forumla that results in a #NA
=LOOKUP(2,1/('[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$B$1:$B$8500&":"&'[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$E$1:$E$8500&":"&'[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$M$1:$M$8500&"Team Leader"&":James Kinsey"&":""Privilege"),'[Monthly%20Agent%20AHT%20Tracker%20Current.xls]Quote'!$Q$1:$Q$8500)
View 12 Replies
View Related
Aug 22, 2006
We have an Excel file that is located on the network and is accessed by numerous individuals. Should someone need access when another user has the file open, we get a message that the file is currently in use and it also lists the user id of that person. The issue is that a particular user will open the file and keep it on their desktop for hours at a time. Is there a way to limit the amount of time that user can have the file open? Is there something within Excel that I can turn on that should the file not be modified for a particular amount of time that Excel saves the file and closes it down?
View 4 Replies
View Related
Oct 12, 2006
is there a combo boxes limitation in a certain worksheets or workbook.?
if there is then how many? coz i got a problem , i have to make around 2000 combo boxes, if i save it , i got no problem , but when i closed it and then reopen , the problem occurs..
View 4 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
Mar 27, 2014
I recently bought a new Dell XPS 10 tablet with a keyboard. When I copied my spreadsheets from my old notebook, none of the countifs or countif functions are working, all popping out a zero. It work completely fine on my old notebook on which I have Excel 2010 installed. On the tablet I have the pre-installed Home & Student 2013 RT. Could this version have limitations to my functions? What can I do to get my functions working again?
View 4 Replies
View Related
Nov 19, 2013
I got this wonderful, beautiful spreadsheet/template i'm using now for my small business, and well, i've got it all tweaked and tuned so it looks beautiful on my screen (100% view in excel). I can look at it, and they way it looks, is the way it prints whether i opt for a PDF or (EEEEEK!) paper. I've sent this report and template i've put together with Mrexcel's support, and it's all primp and pretty and the person i send the spreadsheet to for review? he's looking at it at the same 100%, and lines are cut off when multiple rows are merged that have multiple sentences typed (technically reference to) in. he's tried to print both pdf and paper from his computer and had no issue. it comes out perfect. It only looks visually cut off. we're trying to avoid a second transmission back to me to reformat say something needs to be omitted. QA if you will.
my question: Is this some sort of issue relative to our screen resolution? is there a way i can optimally get what he is seeing when he views it electronically to be what i'm seeing electronically?
View 2 Replies
View Related
Jun 15, 2007
I am writing a macro that scrapes information from other workbooks. I wanted to add a hyperlink on each line to the workbook that row's information came from. I tried both the VBA method of adding a hyperlink as well as the formula method. When I click on the link that was created, windows explorer opens up to a folder rather than opening the workbook. I have a feeling I am hitting some folder level limitation. The link is to a network share and here is an example of what it looks like:
\MYCOMPANY.CORPFILESCBOSEALDRIVEDeptROPSPT Billing2007 AdjustmentsMCBDApproved#01305.20075.25.07 - C - NAME.xls
View 2 Replies
View Related
Jul 4, 2007
I'm developing a few workbooks that may at some point be distributed amongst other people. If this happens, I don't want people sharing these workbooks around, and therefore I need a way of enforcing the users to only use a workbook on a single computer.
I have come up with a method that allows a workbook to open on a single machine only, and not others, but I had to devise this method myself. I was wondering if there are other ways of doing it, possibly quicker ways than my own, or if there is something already within Excel that caters for this?
View 9 Replies
View Related
Jan 12, 2010
Have two ranges:
Range("Master") 5000R x 500C
Range("Filtered") 5000R x 500C
1st Column in Master is a calculated trigger column that sets itself to the row number if it needs to be copied to the Filtered range or is 0. .
Currently using two loops:
Loop 1 Build Collection of unique row numbers from the trigger column.
Loop 2 Process Collection: range copying row values from master to filtered.
View 9 Replies
View Related
Jul 18, 2006
some method by which a user can insert a row that will then copy any forumulas, not their resultant value, in the row above into the new row. The best would be for a user to be able to just use Insert > Row from the toolbar and then have a macro automatically run, but I am willing to use any possible solutions.
I know that there are other posts on this subject, but I can not figure out how to make the information in them work for me. I know that I need to go to View Code in order to insert the code, but I am not clear on what to do after that in order for the code to function, even how to exactly enter the code (should I enter into General or Worksheet, into Sheet1 or ThisWorkbook, etc..). I saw someone mention something about using now() somehow to get code to automatically run or using toolbar buttons, both of which I do not understand how to do.
View 9 Replies
View Related
Apr 23, 2007
I was offered a tip to use Range.Value rather than copying. My syntax fails. I desire to have C2:C equal in D2:D and E2:E.
error: Method "Range" of object'_Worksheet' failed
Range("C2:C" & LRow).Value = Range("D2:E").Value
View 4 Replies
View Related
Jul 20, 2013
I am using excel 2002 and I have found that when the workbook is shared and getting 2 or more users to open the workbook around the same time brings up a read only type prompt box for the 2nd ( and 3rd) user when the book hasn't fully opened for the 1st user/is still fully opening. Is this a limitation within excel or is there a way around this for example via vb code.
I know the more data a workbook has can cause it open a lot slower but is this the same for shared mode.
View 5 Replies
View Related
Dec 29, 2011
I'm trying to do a nested formula to automatically assign a region number based on what the County is. We have 88 counties we deal with, split into 7 regions. What is the best way to go about setting up a formula to do this, so I don't have to do it manually? I believe the last time I tried doing something like this, I received an error saying that Excel didn't support a formula as big as what I was writing.
View 9 Replies
View Related
Jun 14, 2007
I am trying to use the validation feature to calculate a check digit and the formula is too long to put in validation. I rewrote the formula to be shorter using math features instead of text, but apparently you can't use arrays in validation. Because of certain distribution constraints, using VBA is not possible so a UDF is out. Can either of these be shortened in anyway? I am not sure if it is even technically possible to solve this problem with these constraints.
Here is the "human readable" explanation of formula:
(Accept only 10 digit numeric value.)
Example Number: 123456789-3
Digits 1,3,5,7,9 are multiplied by 2 (weighted 5)
So 1,2,3,4,5,6,7,8,9 becomes 2,2,6,4,10,6,14,8,18Weighted 5's multiplied values then have their individual digits added (ex:12=1+2=3)
So 2,2,6,4,10,6,14,8,18 becomes 2,2,6,4,1,6,5,8,9
Weighted 5's sum is added to the sum of the Nonweighted 4 digits.
So 2,2,6,4,1,6,5,8,9,3 becomes 43
Add 24 to sum.
43+24 = 67
Checksum = (10-(67 mod 10)) mod 10
(Sum round up to 10 minus sum)
Method 1 ...
View 9 Replies
View Related
Aug 7, 2012
Is the a more efficient way of sorting a huge sheet (One Column only)? Currently, I use the below code and sometimes it throws an error (due to insufficient memory/ cannot undo this step)
VB:
Sub sort1()
Rows("1:1").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.sort.SortFields.Clear
[Code] .....
View 3 Replies
View Related
Feb 9, 2007
I think Match has trouble finding a value thats within a cell containing a lot of words.
Heres the example. I'm trying to find the word "Hello world" in cell B1, in Column A.
Column A
1 Lots of Text
2 Text Text. Hello world. text text
3 Text Text
Cell B1
Hello world
Column B2
Match("*"&B1&"*",A:A,0)
When theres only 1 sentence in Cell A2, the function works and returns 2.
However, when Cell A2 contains a few paragraphs the function returns #NA.
I don't necessarily need to use Match, if this is indeed a limitation does anyone of suggestions to work around, other than vlookup?
View 14 Replies
View Related
Feb 28, 2007
Excel 2003. When the Vlookup List becomes impractably long to select data from the cell dropdown. Is there an alternative prefered method to entering data from the long list?
View 7 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
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
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
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
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
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
Oct 23, 2009
what is wrong in this code, it does not paste.
HTML Range("A65536").End(xlUp).Offset(0, 0).Select
ActiveCell.Offset(-1, 7).Select
ActiveCell.Copy Destination:=ActiveCell.FormulaR1C1 = "=(R[1])"
I am trying to copy from the selected cell and paste one cell below(next row) in same column.
View 2 Replies
View Related
Dec 21, 2012
I have a range of purchase order rows, with the formulas stored in the first row (TemplateRow) which is hidden. The users may add any number of rows to this range, depending upon the number of different products being purchased.
Code:
'Copy the template row into the first newly inserted row
Rows(TemplateRow).Copy Destination:=Rows(insertionPoint)
'fill down from the inserted row down to the last new row
With Rows(InsertionPoint & ":" & NumberOfInsertedRows.Rows.Hidden = False
.FillDownEnd With
The problem is copying the formulas down to the new rows can take terribly long (minutes) in scenarios of thousands of products. Is there a faster method of copying down my formulas?
View 9 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
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
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
Feb 16, 2007
When I run this code I get a run time error 1004, copy method of range class failed, I have an autofilter on and it is showing 5 rows, the 5 cells in column A of sheet2 gets copied to sheet1 OK but I get the error?
What do I need to do to fix this?
View 9 Replies
View Related