Auto Filter Paste Error

[eckert1961]

Hello,

I'm running Excel 2003 and I have a workbook with an Auto List in Column G. When I attempt to paste a row of data I get the following message.

The operation is not allowed. the operation is attempting to shift cells in a list on your worksheet.

To work around this, I have to convert the list to a range, copy the data and then reapply the Auto List.

Is there another way to work around this or will I have to create vba code or record a macro?

Thanks,
Chris

[VBA Noob]

I can't be sure from the description but it sounds like you are trying to insert a row which pushes over data off the sheet.

See what your last used cell is by pressing F5 > special > lastcell

VBA Noob

[eckert1961]

Hello VBA Noob,

I did what you suggested and the lastcell is 1 row below the last row of data and 1 column to the right. Not sure what that indicates.

I attached a sample worksheet. If you select a row, copy it and then insert copied cells you will see the error that I noted. The auto list you'll see is in G8.

Chris

[VBA Noob]

The attachment allows me to insert a row manually and by using the macro InsertBlankRow

Have you tried to close the file and reopen it?

VBA Noob

[eckert1961]

Hi Noob,

I have no problem inserting a blank row. The problem occurs when I copy a row with data, such as row 19 and then insert copied rows above row 14 as an example.

In my main workbook I will have to paste rows of new projects from a different workbook into my main one.

For testing purposes I'm doing the copy and paste in the same worksheet.

Chris

[VBA Noob]

Hi Chris,

Again I had no issues. Maybe transfer the info into a new workbook?

VBA Noob

[eckert1961]

Interesting. The only way that I can successfully insert copied cells if I first convert the list to a range and then paste the row. After the row has been pasted I have to recreate the auto list.

I'll try your suggestion and report back.

Chris

[eckert1961]

Hi Noob,

I transferred the info to a new workbook, test5.xls that I attached to this post and I still get the error. If you could can you please follow the following steps and see if you can reproduce the error?

1) Verify that you see the LIST in G8. I created this by selecting LIST from the DATA menu and then selecting the cells in Column G. When you click on that cell you should see a down arrow that when you click on you'll see the following listed.

SWD Blades
SWD NAS
SWD Nearline

2) Click on Row 14 and copy it to the clipboard

3) Right Click on Row 10 and Insert Copied Cells

You should get the error.

Chris

[VBA Noob]

1) Verify that you see the LIST in G8. I created this by selecting LIST from the DATA menu and then selecting the cells in Column G. When you click on that cell you should see a down arrow that when you click on you'll see the following listed.

No data validation on that workbook or the first workbook. I press F5 > special > data validation on both sheets

VBA Noob

[eckert1961]

Thanks Noob,

This is the first time that I applied a LIST in a workbook. What would you recommend I set data validation to?

I tried setting it to LIST and then selected the cells in column G. When I attempt to insert copied cells I get the following message.

This operation is not allowed. The operation is attempting to shift cells in a list on your worksheet.

Chris

[VBA Noob]

I would store a list of unqiue items in another worksheet

http://www.contextures.com/xladvfilter01.html#FilterUR

Then name the range
http://www.contextures.com/xlDataVal01.html#Name

Then refer to the name range
http://www.contextures.com/xlDataVal01.html#Apply

You can hide the sheet with the list if you wish to avoid people changing the list

VBA Noob

[eckert1961]

Thanks Noob. I'll give this a try.

Chris

[eckert1961]

Hello VBA Noob,

I reviewed the information on the provided links and attempted to implement this on my spreadsheet but it isn't working the way that I require.

What I need is when a selection is made from the G8 LIST then only the rows that meet that criteria are displayed. Currently all that is happening is, I make a selection from the list and only the name in cell G8 changes. Nothing is filtered. For example, if I select SWD Nearline then only rows 18 and 19 should be displayed.

The attached spreadsheet contains my attempt.

I am hoping that you can fill in the blanks so that it works as desired.

Thanks,
Chris

[VBA Noob]

This post has now gone way off course. What happen with the orginial problem ??

Try this event macro. Right click sheet name (Business Sub Group) > select view code > paste in the below

Please Login or Register  to view this content.

VBA Noob

[eckert1961]

Hi Noob,

I don't believe we strayed off course. If anything I may not have explained the problem clear enough.

The good news is that I copied your code to the sheet module and now I can filter the list by Business Sub Group and I can Copy rows and Insert Copied Cells without any problem.

There are 2 more questions for you. If I click on cell G8 I see 2 list arrows displayed. I can click on either one, select the sub group and the list is filtered correctly. Is there anything that can be done so that only 1 arrow is shown?

Lastly, when I select a sub group the Header changes to that selection. Is there any way to keep the Header as Business Sub Group?

Thank you for your assistance and I'll try to be clearer in the future.

Regards,
Chris

[VBA Noob]

I would look at Advance filter then. See link

http://www.contextures.com/xladvfilter01.html

VBA Noob

[eckert1961]

Will do!!! Thanks again.

Chris

[VBA Noob]

Or just move the validation cell to G7

Please Login or Register  to view this content.

VBA Noob