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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Have you considered deleting or adding rows versus cutting and pasting, using the same criteria for the number of inserts or deletes?
 

JKMyx

New Member
Joined
Nov 9, 2016
Messages
8
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...
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,645
Messages
5,626,062
Members
416,159
Latest member
CheeseBurger5

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
Top