Hi there i have a data table, as seen from the picture, which changes from time to time. For example if there new data coming up for March'15 i would have to copy and overlap cells from April'14 onwards to March'14. Thereafter i would fill in the information for march'15 on the blank cell that was previously filled with feb'15 information. I was wondering if there is any vba code to help move or shift the range to the preferred row of cells that already has existing data ( more of a code to overlap/ overwrrite cells through moving/shifting up) .<br /><a href="https://i.stack.imgur.com/O0A9O.png" rel="nofollow"><img alt="Data Table " class="b-lazy" data-src="https://i.stack.imgur.com/O0A9O.png" data-original="https://i.stack.imgur.com/O0A9O.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>
Was thinking of Activecell.offset but i am not sure if it can shift up as a range of cells.Answer1:
In that case, use a simple loop to copy the values. The last row (13) is overwritten with the empty values of row 14.
Public Sub MoveUp() Dim sh As Worksheet Dim x As Long, y As Long Set sh = ActiveSheet ' or the specific sheet ' The 12 months For y = 2 To 13 ' Your 7 columns For x = 1 To 7 sh.Cells(y, x) = sh.Cells(y + 1, x) Next x Next y With sh.Cells(13, 1) .Select ' This only works if your column A contains dates (not strings) .Value = DateAdd("m", 1, sh.Cells(12, 1)) End With End Sub
Remove the DateAdd line at the end if your column A contains strings, not dates.Answer2:
There are probably multiple ways to do this, but a simple one is (straight from the macro recorder of moving a range and overwriting the target):
Range("A3:G13").Cut Destination:=Range("A2:G12") Range("A13").Select