Cell Randomly Changing

[JRR1229]

I have a spread sheet that I have to add a new row of information randomly. I have created a Macro to make this easier for me and to make sure all my other tabs update correctly as well. There is only one thing that I do not understand why it keeps happening. One cell, the same cell every time I use said Macro, changes the name to the one above it. I do not see any eye catching issues with my macro. Below is the
Sub InsertLine()

'Nameing Items
Dim Password As String
Password = "NEXTELECTRIC"
Dim RowInsert As String
Row_Insert = "A:L"
Dim FillDown As String
Fill_Down = "B:D"
Dim M As String
M = "Main"
Dim W As String
W = "Workings"
Dim FillDown_Range As String
FillDown_Range = "B5:I5"
Dim Message As String
Message = "Press Shift+Ctrl+P After Done Editing"
Dim Message_Name As String
Message_Name = "Protect Reminder"


'UnProtect Sheet

ActiveSheet.Unprotect (Password)

'InsertRow

ActiveCell.Columns(Row_Insert).Insert Shift:=xlShiftDown
ActiveCell.Columns(Fill_Down).FillDown

'Update "Main" Sheet

Worksheets(M).Unprotect (Password)
Range(FillDown_Range).FillDown
Worksheets(M).Protect (Password)

'Bring Back to ActiveCell

Worksheets(W).Select

'Message Box Reminder To Protect

MsgBox Message, vbOKOnly, Message_Name

End Sub

 

[StephenCrump]

Welcome to the Forum!

I'm not clear what your code is doing, but I am guessing the problem is with this line:
Range(FillDown_Range).FillDown

This is an unqualified Range reference, hence it will refer to the ActiveSheet, which looks like "Workings". But it looks like you intend the line to apply to "Main".

If you qualify your references, you can avoid relying on ActiveSheet, .Select etc. Similar comment applies to using ActiveCell. It's far better to specify the cell, rather than relying on the ActiveCell being in the place you think it should be!

So perhaps something like:

Dim wsMain As Worksheet

'...
Set wsMain = Worksheets("Main")

'...
With wsMain
    .Unprotect (MyPassword)
    .Range("B5:I5").FillDown
    .Protect (MyPassword)
End With

A couple of final comments. It's good to err on the cautious side and not use variable names that may be reserved by VBA. Password and FillDown shouldn't cause any problems, but I'd change them to be on the safe side, e.g. to MyPassword and Fill_Down, say.

I also notice that you have declared FillDown but used Fill_Down. Innocent little typos like this are hard to spot, and may cause you hours of grief in debugging.
Always use Option Explicit and you won't have this problem. Google "Always use Option Explicit" for more explanation.

 

[JRR1229]

This code is allowing me to click on a cell in the Workings Spreadsheet of the Excel booklet, inserting a row, auto filling basic information from the above cell that are the same, updating the Main Spreadsheet (which is the spreadsheet that is pulling the information from the Workings Spreadsheet), and then bringing me back to the cell I was originally on. If that helps.

I have made some of the changes you have mentioned but not all seeing that would change what the code is suppose to do. It is still change one particular cells information that I never selected.

 

[StephenCrump]

Thanks, I can follow your code. What was unclear was your layout, where the ActiveCell is, and what you meant by add a new row of information randomly.

Can you post the code you're using now? It would also really help if you could identify the particular cell that's being changed, i.e. the worksheet name and cell address.

I'm about to go off-grid for a week, so I am hoping someone else will be able to jump in and help.

 

[JRR1229]

I've attached a section of the Workings Sheet. The code is helping me click on any cell in column E, insert a new row from E:Q, and Fill Down some of the information that stays the same. The cell that keeps on changing is I5. It should correspond with the company the person is from, but for some reason whenever I use this Module it changes the company to the one above it. In attached image it should say Gexpro not Cresent. The Main Sheet is working just fine.

 

[JRR1229]

My apologies I enter the code part in the wrong spot.

I've attached a section of the Workings Sheet. The code is helping me click on any cell in column E, insert a new row from E:Q, and Fill Down some of the information that stays the same. The cell that keeps on changing is I5. It should correspond with the company the person is from, but for some reason whenever I use this Module it changes the company to the one above it. In attached image it should say Gexpro not Cresent. The Main Sheet is working just fine.

Sub InsertLine()

'Nameing Items
Dim MyPassword As String
MyPassword = "NEXTELECTRIC"
Dim RowInsert As String
Row_Insert = "A:N"
Dim FillDown As String
Fill_Down = "B:D"
Dim M As String
M = "Main"
Dim W As String
W = "Workings"
Dim Fill_Down_Range As String
Fill_Down_Range = "B5:I5"
Dim Message As String
Message = "Press Shift+Ctrl+P After Done Editing"
Dim Message_Name As String
Message_Name = "Protect Reminder"


'UnProtect Sheet

Worksheets(W).Unprotect (MyPassword)

'InsertRow

ActiveCell.Columns(Row_Insert).Insert Shift:=xlShiftDown
ActiveCell.Columns(Fill_Down).FillDown

'Update "Main" Sheet

Worksheets(M).Unprotect (MyPassword)
Range(Fill_Down_Range).FillDown
Worksheets(M).Protect (MyPassword)

'Bring Back to ActiveCell

Worksheets(W).Select

'Message Box Reminder To Protect

MsgBox Message, vbOKOnly, Message_Name

End Sub

 

[StephenCrump]

As I guessed in Post #2, the problem is with Range(Fill_Down_Range).FillDown. You haven't qualified the range reference with a sheet name, so it will default to the ActiveSheet.

So your code is doing this to Workings (the ActiveSheet):

Book5
	A	B	C	D	E	F	G	H	I	J	K
1
2		BEFORE CODE
3		MyLine1	MyLine1	MyLine1	MyLine1	MyLine1	MyLine1	MyLine1	MyLine1	MyLine1	MyLine1
4		MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2
5		MyLine3	MyLine3	MyLine3	MyLine3	MyLine3	MyLine3	MyLine3	MyLine3	MyLine3	MyLine3
6		MyLine4	MyLine4	MyLine4	MyLine4	MyLine4	MyLine4	MyLine4	MyLine4	MyLine4	MyLine4
Workings

Sub Test()

    Dim Fill_Down_Range As String
    Fill_Down_Range = "B5:I5"
    Range(Fill_Down_Range).FillDown

End Sub

Book5
	A	B	C	D	E	F	G	H	I	J	K
1
2		AFTER CODE
3		MyLine1	MyLine1	MyLine1	MyLine1	MyLine1	MyLine1	MyLine1	MyLine1	MyLine1	MyLine1
4		MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2
5		MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine2	MyLine3	MyLine3
6		MyLine4	MyLine4	MyLine4	MyLine4	MyLine4	MyLine4	MyLine4	MyLine4	MyLine4	MyLine4
Workings

To apply the same .FillDown to the Main sheet, you could do this instead:

    With Worksheets(M)
        .Unprotect (MyPassword)
        .Range(Fill_Down_Range).FillDown
        .Protect (MyPassword)
    End With

 

[JRR1229]

Thank you I got it to work with this fix and a little other adding ending code

Sub InsertLine()

'Nameing Items
     Dim MyPassword As String
          MyPassword = "NEXTELECTRIC"
     Dim RowInsert As String
          Row_Insert = "A:N"
     Dim FillDown As String
          Fill_Down = "B:D"
     Dim M As String
          M = "Main"
     Dim W As String
          W = "Workings"
     Dim Fill_Down_Range As String
          Fill_Down_Range = "B5:I5"
     Dim Message As String
          Message = "Press Shift+Ctrl+P After Done Editing"
     Dim Message_Name As String
          Message_Name = "Protect Reminder"
     

'UnProtect Sheet
     
     Worksheets(W).Unprotect (MyPassword)
     
'InsertRow
     
     With Worksheets(W)
          ActiveCell.Columns(Row_Insert).Insert Shift:=xlShiftDown
          ActiveCell.Columns(Fill_Down).FillDown
          LastRow = Range("F65536").End(xlUp).Row
          Range(Cells(2, "F"), Cells(LastRow, "F")).FillDown
    End With
    
'Update "Main" Sheet
     
     With Worksheets(M)
          .Unprotect (MyPassword)
          .Range(Fill_Down_Range).FillDown
          .Protect (MyPassword)
     End With

'Bring Back to ActiveCell
     
     Worksheets(W).Select

'Message Box Reminder To Protect

     MsgBox Message, vbOKOnly, Message_Name

End Sub