Copy & Paste Row

[aimanraya]

The Attachments contains a macro copying rows from a sheet and Paste to another sheet based on the row number , I want to copy and paste based on sequence in column A .

[RudiS]

This code will insert the rows at the position (cell selected by the user)...

Please Login or Register  to view this content.

This code will insert the row at the position (number typed by the user)...

Please Login or Register  to view this content.

[aimanraya]

The first code ,when the InputBox show to Select the cell that you want to start at , I can select only one because the InputBox show is active so I cant move down (it prevent me ). Also the second code , there is any way to able me move down sheet during InputBox show , I hope you understan me .

[aimanraya]

The second code , When I typed number 50, 100 , 150 .. It paste row below 49 , 99 , 149 . Also when I enter a number that does not Found in sequence, can show MsgBox " was not found!" ? Sorry I dont remark '+ 1 (Uncomment this line to insert rows below NumberRow) . only I want MsgBox if I typed any number not found , very tanks dear Rudis .

[RudiS]

Hi,

In both the code blocks, the rows will now be inserted *below* the indicated number.
Since the inputbox is an Application.Inputbox(...) it does allow for scrolling to numbers further down the list. In other words, when the inputbox is showing, you can either use the mouse wheel to scroll down, or you can drag the scroll bar of Excel down.

Please Login or Register  to view this content.

[aimanraya]

The second code is very very well , I prefer to use , But the first the same problem ,when I Select the cell that i want to start at , then I select agree , then " Do you want to paste again ?" I select YES , After this I cant paste again , the mouse wheel do not scroll down .

[aimanraya]

I greet you, you're a genius

[RudiS]

Hello Aiman,

I tested the first macro and I understand that you mean. Sorry about that. It's something I overlooked.
I have fixed this one too (even though you say you prefer macro #2).

Have a great day.

Please Login or Register  to view this content.

[aimanraya]

Excuse me, request a last resort , If I want to copy a single row of sheet and paste more than one row in another sheet based on the row number , I want to copy and paste based on sequence in column A . Please see the attachment

[RudiS]

Hi,

Try this code....

Please Login or Register  to view this content.

[aimanraya]

Both of two codes does not work when the sheet has protected .

[RudiS]

Hi,

You can add a line: Activesheet.Unprotect at the top of the macro.
And a line: Activesheet.Protect at the very bottom of the macro.

Do this for whichever macro you are using (and if the sheet has a password of course).

Please Login or Register  to view this content.

[aimanraya]

Fantastic, I tried to the last code , I will try to the others , and tell you .

[aimanraya]

I am grateful to you, and best wishes to you for success .

[RudiS]

Good to know...
Enjoy!

[aimanraya]

New problem :
The sequence in column A is not values But formulas, and this led to the macro performs based on the row number and not the serial (The sequence) in all codes.

[RudiS]

If the column contains formulas, then we need to change the Find command to search for *values* and not the *true* content of the cells.

The following version should work:

Please Login or Register  to view this content.

[aimanraya]

Thank you very much ,I replaced the old line with this new below line, and all things very good .

lStart = Range("A:A").Find(What:=NumberRow, LookIn:=xlValues).Row + 1

[aimanraya]

If NumberRow = 0 Then Exit Sub

If NumberRow1 = 0 Then Exit Sub

because of the above two lines , If I do not enter any value "Cancel" , the protection would be canceled , I used Else in the first line instead of Exit Sub , so it is good , but it is difficult to use Else again in the other line .can you modify please ? .

[RudiS]

Hi,

This will re-enable the protection if you cancel the row number message...

Please Login or Register  to view this content.

[aimanraya]

Thank you very much for your help, God bless you .

[RudiS]

Thank you. It's a pleasure to assist.