0

I created (partly copied) a macro that, depending on the selection in the first sheet, adds a row in the first and the second sheet in the same place.

Yesterday morning it all still worked fine, but after doing some testing off all the different functionalities (mostly AutoFilters) it suddenly decided to not use the targeted cell anymore and instead target one at random. It even changes the random cell due to unknown circumstances (yesterday it was one in the first row, then in the 11. etc.).

Im very new to VBA and would be really thankfull for some help with this issue.

Heres my code for adding a row (removing works on the same principle): 

Sub Add_Row()

    Application.ScreenUpdating = False

    ActiveSheet.Unprotect
    With ActiveWorkbook.ActiveSheet
        If .FilterMode Then
            .ShowAllData
        End If
    End With

    Sheets("People to Project").Select
    ActiveSheet.Unprotect
    With ActiveWorkbook.ActiveSheet
        If .FilterMode Then
            .ShowAllData
        End If
    End With

    r0w = ActiveCell.Row
    c0l = ActiveCell.Column

    ThisWorkbook.Sheets("Overview").Cells(r0w, c0l).EntireRow.Insert
    ThisWorkbook.Sheets("People to Project").Cells(r0w, c0l).EntireRow.Insert

    Sheets("Overview").Select
    Range(Cells(r0w, 17), Cells(r0w, 48)).Select
    Selection.Style = "1. Projectfield"

    Sheets("People to Project").Select

    ActiveSheet.Unprotect

    Range(Cells(r0w, 12), Cells(r0w, 43)).Select
    Selection.Style = "1. Projectfield"

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                        False, AllowFormattingCells:=True, AllowFiltering:=True

    Sheets("Overview").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
                        False, AllowFormattingCells:=True, AllowFiltering:=True

    Application.ScreenUpdating = True

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
JJBerger
  • 1
  • From what I can see `r0w` and `c0l` comes from `People to Project`. All other range references then use those row & column numbers even though it's a different sheet. I can't see anywhere where it would select a random cell - it's all based on which cell is selected in `People to Project`. – Darren Bartrup-Cook Dec 13 '18 at 10:13
  • Arrange your screen so you can see the sheet and code, comment out the `ScreenUpdating` line and then step through the code with `F8` so you can see what cells/sheets are being selected. Have the `Locals` window open in the VBA screen so you can see the values of the various variables as well. – Darren Bartrup-Cook Dec 13 '18 at 10:16
  • you should try to avoid using `select` in VBA. See here https://stackoverflow.com/a/10717999/11766312 – Miles Fett Aug 20 '19 at 19:47

1 Answers1

0

Thank you for the quick Help!

The following code after the filters were deactivated solved my problem:

Sheets("Overview").Select

Off to further adventures with VBA :)

JJBerger
  • 1