Cell Randomly Changing

JRR1229

New Member
Joined
Jun 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,850
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Jun 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Sep 18, 2013
Messages
3,850
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Code:
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.
 

Attachments

  • Mr. Excel Address Book Cell Change 1.png
    Mr. Excel Address Book Cell Change 1.png
    87.6 KB · Views: 1

JRR1229

New Member
Joined
Jun 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA 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

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
 

Attachments

  • 1601662415056.png
    1601662415056.png
    110.6 KB · Views: 1

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,850
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
1
2BEFORE CODE
3MyLine1MyLine1MyLine1MyLine1MyLine1MyLine1MyLine1MyLine1MyLine1MyLine1
4MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2
5MyLine3MyLine3MyLine3MyLine3MyLine3MyLine3MyLine3MyLine3MyLine3MyLine3
6MyLine4MyLine4MyLine4MyLine4MyLine4MyLine4MyLine4MyLine4MyLine4MyLine4
Workings


VBA Code:
Sub Test()

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

End Sub

Book5
ABCDEFGHIJK
1
2AFTER CODE
3MyLine1MyLine1MyLine1MyLine1MyLine1MyLine1MyLine1MyLine1MyLine1MyLine1
4MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2
5MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine2MyLine3MyLine3
6MyLine4MyLine4MyLine4MyLine4MyLine4MyLine4MyLine4MyLine4MyLine4MyLine4
Workings


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

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

JRR1229

New Member
Joined
Jun 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Thank you I got it to work with this fix and a little other adding ending code
VBA 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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,571
Members
410,852
Latest member
WernerS
Top