VBA - Automatic select, cut and paste macro

JKMyx

New Member
Joined
Nov 9, 2016
Messages
8
Hi all,

I'm stuck with a last bit of my sheet that needs a macro.
I thought it would be easy, but it seems harder than expected....

In cell B2 I have a number; when I increase or decrease that number, I would like all the entries from D5 til J5 and all rows below, to be cut and pasted accordingly by the number of rows of the figure that I have increased or decreased.

So for example on the picture below,
in B2 I have "3"
- If I change this figure to "4", range D5:J8 , should be cut and paste one row lower, hence in row 6.
- If I change this fugure to "2", range D5:J8, should be cut and paste one row higher, hence in row 4.

Rules for the range
- The row for the start of the range is determined by the fact that there's value in column B (nr of suppliers), below the figure that I have just changed. (In the example, if I change cell B2, first figure to be found in column B is on row 5).
- The row for the end of the range is 100 rows more.
- Columns D to J.

Any ideas for the macro?
Thanks a lot!
Help is much much appreciated!


22.PNG
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Have you considered deleting or adding rows versus cutting and pasting, using the same criteria for the number of inserts or deletes?
 
Upvote 0
Hi JLGWhiz,
Thanks for your answer.
Yes of course, but I simplified the issue to represent it here; this problem is part of large sheet with multiple macro's... Unfortunately I cannot add or delete some rows, it would create problems with my other macro's...

I really think the cutting and the pasting are the only option... It's just that the code is complicated to do so...
 
Upvote 0
Moving the data around would also have an effect on the results of the other macros, wouldn't it? I don't think I will attempt to write code without being able to see the sheet and its formulas and macros. Maybe someone else will take a shot at it.
Regards, JLG
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top