Easy Example: What I basically need is when the value of cell A4 < 1, it
will cut and paste the ENTIRE rows B-H to now be rows A-G

Any help appreciated, thanks!


--
dstock
------------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php...o&userid=24225
View this thread: http://www.excelforum.com/showthread...hreadid=380981

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$4" Then
If Target.Value < 1 Then
Columns("B:H").Copy Columns("A:G")
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

"dstock" wrote in
message ...

Easy Example: What I basically need is when the value of cell A4 < 1, it
will cut and paste the ENTIRE rows B-H to now be rows A-G

Any help appreciated, thanks!


--
dstock
------------------------------------------------------------------------
dstock's Profile:
http://www.excelforum.com/member.php...o&userid=24225
View this thread: http://www.excelforum.com/showthread...hreadid=380981

sub macro
if range("a4").value<1 then

Range("A4:F8").Select
Selection.Cut
Range("G16:L20").Select
ActiveSheet.Paste


end if
end sub

you can change the copy and paste range


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380981

Thanks for the quick reply. Adding to that. Lets assume I have a 13x16
block of cells. (So rows are 1-13 and columns A-P)

We can ignore everything row seven and above spanning all cells.

When B7 < 1, every cell below, so 8-13 move up one leaving row 13
empty.

Is the code for this:?

sub macro
if range("B7").value<1 then

Range("A8:P13").Select
Selection.Cut
Range("A7:P12").Select
ActiveSheet.Paste


end if
end sub

Thanks!


--
dstock
------------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php...o&userid=24225
View this thread: http://www.excelforum.com/showthread...hreadid=380981

Yes I think you are correc

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=38098

Thanks for the replies. The the block of cells is "shifted" up, I need
to keep the name of first cell. So if I move the block A12:H17 to
A11:H16, I need to keep the name of the cell A11.
Thanks very much. :)


--
dstock
------------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php...o&userid=24225
View this thread: http://www.excelforum.com/showthread...hreadid=380981

sub macro
dim temp as variant
if range("B7").value<1 then
temp=range("a11").value
Range("A12:H17").Select
Selection.Cut
Range("A11:H16").Select
ActiveSheet.Paste
range("a11").value=temp

end if
end sub

the above will keep the value of cell a11 and shifts one row up


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380981

That didn't work. I added a new button and copied the code into vba
Perhaps an easier thing to do would be to simply rename the cell afte
the cut and paste. It needs to be renamed "bal-mo" I'll tinker with i
a little bit to see if I can get it. Thanks

--
dstoc
-----------------------------------------------------------------------
dstock's Profile: http://www.excelforum.com/member.php...fo&userid=2422
View this thread: http://www.excelforum.com/showthread.php?threadid=38098