I have a database that lets me add columns and do the required vlookups however, when I filter with a particular rule it throws the 'Cannot Move Objects Off the Sheet' error. Also, I cannot see any rows beyond row number 4316 - could this be the reason for the error?
Under 'Advanced' options, I've gone to 'Display options for this workbook' and chosen the 'All; option under 'For Objects show', however, I'm stick not getting anywhere
Does anyone know the maximum number of rows an autofilter can handle? I have a spreadsheet at work that started to crash at approximately 1500 - 2000 rows. The autofilter would give a weird error "Fixed objects may move" and then the filter would get rid of half the information and the filter arrows would show on a random row (rather than on the title).
I had heard a rumor that autofilter can only handle so many rows of data, but couldn't find where the cutoff point is. The spreadsheet is also shared between multiple people, so i'm sure that adds to it. And yes I know that Excel isnt' supposed to be kept open by multiple people and used as a database,
I am running excel 2007 and I have 23 worksheets in a workbook. I have no links or hidden rows or colums. I have no merged cells or comments. My biggest sheet is A1:
AW8096. I use VB macros to add lines and move data from one set of workbooks to this one.
I have built several sheets using this proceedure.
All of a sudden I get the error "Cannot shift objects off sheet" when I run
a routine I have 50,000 times. This even happens on new blank sheet so I know its' not on the sheet.
I have checked to make sure there are no hidden comments, row or columns. I have deleted most of the worksheets and I still get the error. I have made copies of the workbook and used different file types to save it. I can create a new black sheet in the workbook that is empty and the same thing happens.
The only way I can get it to work is to creat a new blank workbook and copy and paste the sheets into the new workbook. Yes I have serched the FAQ and knowledge base and tried all of those fixes as well.
I'm having problems when trying to apply an auto filter to my data. When I select the critieria from the auto-filter drop down, on any field heading, I get this messgae "Fixed Objects Will Move" about 18 times.
View 9 Replies View RelatedHere is the original table:
This is with a filter on:
You can see there that some inserted object (in this case, PDF files shown as icons), are moved. I need to find a way to immobilize every inserted object within each cell boundaries. I want to freely sort or filter and avoid this kind of problem that gets worse with more rows, columns and inserted objects.
So I'm creating a grid worksheet for engineering calculations and I have a couple questions about the best way to do it. I've been messing with excel for my calcs for about a decade now, and I every once in a while I try and improve them.
First: I will have several input areas that will either be colored text or shaded background (either works for me). I don't want these 'input required' objects to print as color, just black. But I want my logo at the sheet top to print as color. I've only found ways to not print any color. Can I print the logo as color and the 'input required' stuff as black?
Second: When I do calcs by hand, I write them out on 10x10 grid paper. Each 10x10 grid is one inch. In the past I've created this grid out of the cells, which works. I frequently need to change formulas around though, and each time I do this, I end up needing to mess with the grid cells also. Is there a way to create the grid and have it in the background so it doesn't need to be adjusted each time I change formulas? I wan't the grids to print, and also want to see them on the screen, as I sometimes draw simple objects along with the formulas.
I have a Master sheet linked with another worksheets with formula.
Now I want to insert 1 column in between , and when trying to insert the column in it a message sowing that " Cannot Shift Objects of Sheet"
Why this error shwoing and how can i insert any column in it.
I have a big macro that runs in everyone's PC except mine. Once I debug it, it shows that it stocked at copying the objects from a template sheet to another sheet. When I try doing it manually, I still can not copy the objects to another sheet. It copies the sheet but except the objects in it. Is it related to my settings?
View 6 Replies View RelatedI am using a VBA script to protect my workbook and allow grouping on workbook open:
Private Sub Workbook_Open()
With Sheet1
.Protect Password:="", UserInterfaceOnly:=True
.EnableOutlining = True
End With
How can I enable "Edit Objects" with this? I want the user to be able to edit comments as well. I am NOT looking to do this by checking the box when I protect a sheet; this effect is gone once the book is opened again, and the above macro runs.
doesnt work when recording a macro because the group name changes each time I group the pictures (visios) and the picture names change depending on what is on the sheet at the time.
Is there a way to group every picture on a sheet1 (excluding macro buttons), then copy them to sheet 2?
I'm trying to hide a few columns on my spreadsheet. I'm getting a "cannot shift objects off sheet" error message 2010 Excel program. I never had trouble with my 2003 excel.
There are no comments on this sheet, 'Display options for this workbook' already says all. I do have macro buttons on the top... but changed them all to move and size with cells.
I'm getting a runtime 1004 error "Cannot Shift Objects Off Sheet" right at the line when i am trying to collapse a subtotal (showlevels, rowlevel 2). I searched about this and i tested. I cannot find any comments, hidden comments, rows or columns. I cannot find any shapes (ran "Kill_Shapes" posted by Aaron Blood).
Sheets("Oxnard Planning 10 (all)").Activate
'SORT: Del Code (D), then Style (A)
Range("A1").Sort Key1:=Range("D1"), Order1:=xlAscending, _
Key2:=Range("A1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
'Subtotal by STYLE
Range("A1").subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'Subtotal lines = Bold & Pink
ActiveSheet.Outline.ShowLevels RowLevels:=2 '<< ERROR:Cannot shift Objects off Sheet
With Range(Range("K65536").End(xlUp), "A1").SpecialCells(xlCellTypeVisible)
.Interior.ColorIndex = 38
.Font.Bold = True
End With
ActiveSheet.Outline.ShowLevels RowLevels:=3
I want my macro to protect my sheet again after it is done to allow editing objects and formatting rows. I am really stuck and have come up with the below, but it isn't working.
View 5 Replies View RelatedIn the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that will move data by date from an (Entry) sheet to a (Historical) sheet. I want to enter a date in B3 on the (Entry) sheet. I then enter data into C3. What I would like to happen is when the data is entered into C3 the sheet goes and finds the same date that I entered in B3 and copies that data from C3 into Column E of the (Historical) sheet.
Test3.xlsm
http://www.mrexcel.com/forum/showthread.php?t=323741
From the above thread I am using the following macro...
Sub MoveOver()
Dim MoveRange As Range
Application.ScreenUpdating = False
With Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(, 21))
.AutoFilter field:=1, Criteria1:="=C&P"
On Error Resume Next
Set MoveRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter
End With
If Not MoveRange Is Nothing Then
MoveRange.Insert Shift:=xlToRight
End If
Application.ScreenUpdating = True
End Sub
I am getting an error 1004 message saying cells cannot be moved off of the spreadsheet. However, my last cell on the sheet is J28956.
Columns B through J contain data. Columns beyond J do not contain data.
Can someone give advice on getting the macro to work.
I need to move the rows from one sheet to another sheet upon clicking the ‘Submit’ button.
I will manually update my efforts in ‘Sheet 1’ and when I click ‘Submit’, it should “copy” the rows to the next sheet (Sheet 2). However, it should not duplicate the entries irrespective of clicking Submit multiple times. Obviously, while clicking ‘Submit’ it should check the entry for that particular employee name and date in the ‘Sheet 2’ and remove that completely and update the new entries and this should avoid duplicates for that date. Every time when I add new entries for a different name and for dirrerent employee, it should keep appending the Sheet 2.
In addition, it should avoid copying the blank rows from Sheet 1 (S. No column will not be blank usually) to Sheet 2.
All this should happen upon clicking the Submit button.
I am looking for some example code that when I get an error using workbook.open, because a file is corrupt, I can cut from the list and paste into a range to show that file had errors, then delete that row and continue.
For example
My list:
Column A Column B Errors List Error
File path B and name Information File Path A Information
File path C and name Information
File path D and name Information
I can cut and paste File path C into an errors list, then delete the row A and B and shift the below lines up.
using VBA, where if in Column I = "TRUE", move the entire row plus the 1 row underneath to sheet named "PaymentData?
I have an excel spreadsheet,I have a problem and I need a macro , in sheet1 I have several values of 7 numbers in a cell, example 1|2|3|4|5|6|7| up to 93|94|95|96|97|98|99|, which can be found on the columns A, B, C, D, E, F, G, H, I, and I want for example to extract all values that begin with the number 1 ,and to put all the values in the worksheet 2 columns A, B, C, D, E, F, G, H, I,,
For example 1|2|3|4|5|6|7| ,,,,,, up to 1|94|95|96|97|98|99| moved in sheet2 from sheet1.
I tried to extract the filter from the data tab .... sort and filter,,, filter,,,, filter by number,,,, starting with,,,, we apply the filter, but when we selected data values and take that were among the selected what was not good.
I like this :
column A 1|2|3|4|5|6|7| , 21|25|32|43|51|60|73|, 1|2|3|4|52|69|78| ,11|12|30|45|50|61|79| ,3|5|70|74|75|77|79| , 1|22|33|44|50|63|75|, 1|22|33|44|50|63|75|................
column B 1|2|3|4|5|6|23| , 1|2|3|4|5|6|8| , 1|2|3|4|5|6|9| , 1|2|3|4|5|6|10| , 11|21|31|40|52|61|70| , 9|2|3|4|50|68|70| , 1|12|13|24|51|60|77| ,.........
column C 1|2|3|4|50|65|70| , 2|5|7|9|10|16|37| ,5|8|9|14|15|26|67|,,,,,,,,,,,1|94|95|96|97|98|99|.
In these columns I want to extract all values that begin with nr1 .....1|2|3|4|5|6|7|,,,1|90|91|92|93|94|95| from sheet1 in the sheet2, I mean these...
column A 1|2|3|4|5|6|7| ,1|2|3|4|52|69|78| ,1|22|33|44|50|63|75| ,1|20|31|43|55|60|71|
column B 1|2|3|4|5|6|23| , 1|2|3|4|5|6|8| , 1|2|3|4|5|6|9| , 1|2|3|4|5|6|10| ,1|12|13|24|51|60|77|
column C 1|2|3|4|50|65|70| , 1|94|95|96|97|98|99|
How can I code a Picture so it moves atop a excel sheet?
I dont like the behavior this code runs.
ActiveSheet.Shapes("Picture 1").Select
Selection.ShapeRange.IncrementLeft 0.75
i learned how to highlight a row by placing an X in a single cell, my next question is:
Is there a way (most Likely there is) By placing "J/C" into a cell, make the entire row cut and then insert itself into my second sheet on row 2? "insert into a new row and not over existing information?"
now i have done this in the past using a Macro, select the row and then by pressing ctrl+k it would cut the row and then insert it into row 2 of the second sheet.
I want to know the shortcut key for to move one sheet from another sheet without using mouse.
View 3 Replies View RelatedI have a "Log Sheet" with dates in one column and times in another column.
Currently I have a formula which matches the months of the year and totals the time during that month below is this formula. (This formula id on the same sheet.
=SUMPRODUCT(--(MONTH($A$5:$A$1426)=1),--(YEAR($A$5:$A$1426)=2008),$F$5:$F$1426)
Now I am trying to clean up the sheet and I want to move all the results onto another sheet Lets call it sheet 2 and sheet 1
sheet one will have the months and times.
sheet 2 i want the formula in each cell, andto place the result in each respecting cell/
This is what I came up with but why is it not working?
=SUMPRODUCT(--(MONTH(Sheet1!A:A)=11),--(YEAR(Sheet1!A:A)=2007),Sheet1!F:F)
i am trying to get a vba procdeure to copy Sheet "Template" and move to the end, but when i run the code, the code copies the template but does not move to the end, how can the below be modifed?
Sub Macro3()
Sheets("Template").Select
Sheets("Template").Copy Before:=Sheets(6)
End Sub
I convert many large files from csv to Excel. Once I import the csv to Excel I must break the worksheet into multiple depending on when a Cell in column F is equal to a "cata" of which I do successfully with a macro.
The next step in VBA I attempt; is to go 10 cells to the right of the active cell (cata) and anchor that cell address so I may copy the range from "A1" to the anchored cell address.
I have been unable to pass the the value 10 cells to the right of the active cell. I would be grateful of any suggestions.
Here is the VBA Code I've been trying. (It bombs after it finds the cell 10 cells from the active cell.)
Sub SelectRange()
ActiveCell.Offset(0, 10).Select = EndRange
Sheets("Sheet1").Activate
Range("A1,EndRange").Select 'not working'
Selection.Cut
Sheets.Add
ActiveSheet.Paste
End Sub
I have a large document with a lot of names listed. I want to make up new sheets for each person listed. Is there a formula that I can enter that will move the entire row based on the person's name? See attached.
View 3 Replies View RelatedI searched and found a couple of different active.sheet codes but could not get it to work. What I want to do is move the worksheet tab I create in the code below all the way to the end (right) of the worksheet tabs already in my workbook.
View 2 Replies View RelatedIs it possible to create a cell in one sheet, where after data is entered, will move it to another sheet. Additionally, the data in the cell, after the transfer to another sheet is made would delete itself. Once the data is entered, sent to another sheet and deleted, next time data is entered into the same cell, it would be sent into a row below the cell that the first piece of data would be in. Therefore, after I enter data, [for example] press enter, then go back to input some more data, after a while there would be a long list in another sheet. The last feature of the problem (it would be something extra really) is that the function/macro is to be time dependent - during one month the data would stack up in one column, after the next one begins, the next column will automatically start all over.
The idea is to create a budget book - where on the main page I would only have to enter the value in a specific box, the value then would be automatically sorted into the right row and column in another sheet, after the month/week ends, data would start to stack up in a column devoted to a new month/week.
Of course the data could be entered manually in different sheets, but it proved to be too complex to be useful.
Macro, which can cut/Paste from sheet "Pastin" to Sheet "Robot".
The input in sheet Pastin is veritical and the the result is in a row horiontsal.
There can be no references between the names for the columns or rows, only the cell to move.
I would like it to be so when I run the macro it example move it to sheet robot from row A3, and if I do again it make the next one from Row A4.
What I want to move can be seen in below.
Cell on the left is sheet "Pastein" and cells on the right is sheet "Robot"
Special is that one move have to be taken from on cell comma separated input, so it goes to sheet "Robot" for each text/number, between the comma.
I have attached a test sheet to work with also.
[Code] ...
Testmove.xls
So I have a sheet that has 9 columns A - I sheet is called "Door Switch Times"
Basicall when column F gets data in it I need then a macro to automatically Copy B, C, D to a
new sheet called "Main". I need B to be copied to C on Main, "C" on Door Switch Timse to be copied to "D" on main and "D" on Door Switch Times to be copied to "E" on Main. I cant post the sheet as my work computer will not allow me to transfer or share files.