CODEWITHSUNDEEP
vbaexcel
Mustapha
Mustapha

Reputation: 86

Excel VBA insert row and copy data on multiple sheets

I am trying to get the correct VBA code that will allow me to insert a row in to a filtered table at the same place on multiple worksheets and copy all of the content from the entire row above.

There is a check box relating to each sheet in the workbook. If the check box is ticked then the row should be inserted in to this sheet.

The sheets are password protected. The password is found in another of the worksheets in the file.

I have almost got this to work. My file can be found at the following location:

https://drive.google.com/file/d/0B5HnHgSNFkFid0gwbDNMOFN1NUU/view?usp=sharing

The code is as follows:

    Sub Insert_Rows()

    Dim sh As Worksheet

    For Each sh In Sheets
        If sh.Name = "Sheet1" And Worksheets("Sheet4").Range("D1").Value = True Or _
            sh.Name = "Sheet2" And Worksheets("Sheet4").Range("D2").Value = True Or _
            sh.Name = "Sheet3" And Worksheets("Sheet4").Range("D3").Value = True Then
            With sh
            .Unprotect Password:=Worksheets("Sheet4").Range("A1")
                .Cells(ActiveCell.Row, 4).EntireRow.Insert
                .Range(.Cells(ActiveCell.Row, 1), .Cells(ActiveCell.Row, 4)).FillDown
                .Protect DrawingObjects:=True, contents:=True, Scenarios:=True, Password:=Worksheets("Sheet4").Range("A1")
            End With
        End If
    Next sh

End Sub

The issue I am having is that not all of the data from the row above is being copied. The data in the 5th column is not copying down. I am sure it is something to do with the 4 in the code .Cells(ActiveCell.Row, 4). I want it to copy the entire row above regardless of the number of columns.

Any help greatly appreciated.

Thanks

Upvotes: 0

Views: 979

Answers (1)

YowE3K
YowE3K

Reputation: 23984

To FillDown the EntireRow, instead of just the Range between column 1 and column 4, replace

.Range(.Cells(ActiveCell.Row, 1), .Cells(ActiveCell.Row, 4)).FillDown

with

.Cells(ActiveCell.Row, 1).EntireRow.FillDown

(which could also be written as .Rows(ActiveCell.Row).FillDown)


Note:

Please remember that ActiveCell.Row is not necessarily referring to any special location on Sheet1, Sheet2, or Sheet3.

If the currently active cell is cell G67 on sheet Sheet4, then ActiveCell.Row will evaluate to 67 and so row 66 of Sheet1 (and/or Sheet2 and/or Sheet3) will be copied to a newly inserted row on Sheet1 (and/or Sheet2 and/or Sheet3) - it won't magically decide to insert row 58 on Sheet1 and row 82 on Sheet2, etc.

If the only problem you are having is that the entire row is not being filled down, then the solution above will fix it. But if you find that the wrong row is being filled down, then you will need to rethink how you are selecting the row.

Upvotes: 0

Related Questions