Copy a range and shift it down one row on the same sheet

Question

I am fairly new in excel VBA and would really appreciate any help on this matter.

The workbook includes data from range A5:AZ1000 (new client info is inputted in new rows, but some cells may be empty depending on the nature of the case). When a user inputs new client info (begins a new row) I would like the existing data (range A5:AZ1000) to shift down one row, and a blank row to appear in range A5:AZ:5. I would like users to be able to click a macro "New Client" for this to happen.

It should be noted that this is a shared workbook and therefore I cannot have macro that adds a new row.

Here is the code I'm working with:

Sub shiftdown()
' shiftdown Macro
Dim lastRow As Long
Dim lastColumn As Long
Dim rngToCopy As Range
Dim rng As Range
Set rng = ActiveSheet.Range("A1").Value
lastColumn = ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Column
lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
If rng > 0 Then
     ActiveSheet.Range("A5" & lastRow).Select
     Selection.Copy
     PasteSelection.Offset(1, 0).PasteSpecial xlPasteValues
     'Error Object Required
End If
End Sub

Change `PasteSelection.` to `Selection`. but you should learn to avoid using Select see: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Scott Craner, Dec 14 '16 at 18:56
Also you can do this as one row inside the IF: `ActiveSheet.Range("A5" & lastRow).Offset(1, 0).Value = ActiveSheet.Range("A5" & lastRow).Value` and avoid the clipboard. — Scott Craner, Dec 14 '16 at 18:58
@YowE3K it wasn't functional and I thought it might confuse readers of the purpose. I will put it back up. — Ben, Dec 14 '16 at 19:18
The whole purpose (I think) of SO is to turn non-functional code into something that works. Without code showing, the question will be down-voted into oblivion. — YowE3K, Dec 14 '16 at 19:20
I edited the question and rolled it back to Scott's earlier edit, so that the code is formatted nicely. — YowE3K, Dec 14 '16 at 19:25

score 0 · Answer 1 · answered Dec 14 '16 at 19:08

Set rng = ActiveSheet.Range("A1").Value ???

if rng is a range, then replace it by :

Set rng = ActiveSheet.Range("A1")

or if rng is a variable, replace

Dim rng As Range

by

Dim rng As variant
rng = ActiveSheet.Range("A1").Value

another error :

you declared rng as range and then you test if it is > 0

If rng > 0 Then ...

it is not possible

score 0 · Accepted Answer · answered Dec 14 '16 at 19:17

0

Normally I wouldn't answer if the question doesn't include any code to show effort, but I started writing the below while the question actually did show code so I may as well provide it. It may achieve what you are after.

Sub shiftdown()
' shiftdown Macro
    Dim rng As Range
    With ActiveSheet
        If .Range("A1").Value > 0 Then

            Set rng = .Range(.Cells(5, 1), _
                             .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, _
                                    .Cells(4, .Columns.Count).End(xlToLeft).Column))
            rng.Offset(1, 0).Value = rng.Value
            .Rows(5).EntireRow.ClearContents
        End If
    End With
End Sub

answered Dec 14 '16 at 19:17

YowE3K

23,852
7
26
40

Thanks again for your response. The code you provided is moving the data in column A down one row, however, I would need the entire range A5:AZ1000 (last row and column with data) to move down as the macro is clicked and the user may begin to enter new data in row A5:A5. Does this clear the question up for you? – Ben Dec 14 '16 at 19:32
@ben - Hmmm - for my testing I had data up to column L on row 4 (the one being used to test how many columns to copy) and it moved A5:L21 down to A6:L22. Does your AZ4 contain anything? – YowE3K Dec 14 '16 at 19:37
it seems to be working just fine now! My mistake, the validation I had somehow disappeared. Thanks again. – Ben Dec 14 '16 at 19:45

Copy a range and shift it down one row on the same sheet

2 Answers2