Copying Down Formulas to New Row

[KayCee]

I have a membership table that contains details of payments from members. From time to time I need to add members to the membership list (and other sheets) and so I use command buttons to activate VBA code to do this task. The aim is to add a blank row (Row9) just below the top name in the members list, add a new name using a form, and then copy down the formulas from the top row (Row8) to the new Row9 row.

As you will see from the first image, the D8 cell does not contain a formula and so I would not expect the new row D9 cell to contain a formula. However, as you can see from the second image it does contain a formula!

The section of code that I use to add the new blank row, then enter the new members name, then copy down the formulas is shown below.

I would be grateful for any assistance in solving this problem as I cannot see any reason looking at my VBA code why the rogue formula would be inserted in cell D9.


' Enter a new name.


Sheet27.Select ' Select Members sheet

Load AddNameForm

strName = Application.Trim(AddNameForm.SURNAME.Value & " " & AddNameForm.Forename.Value) ' strName dimensioned earlier as a string

If strName = "" Then
MsgBox ("You did not enter a name")
Sheet27.Select 'Members sheet
Exit Sub
End If

If AddNameForm.Forename.Value = "" Then
MsgBox ("You did not enter a Forename")
Sheet27.Select 'Members sheet
Exit Sub
End If

If AddNameForm.SURNAME.Value = "" Then MsgBox ("You did not enter a Surname")
Sheet27.Select 'Members sheet
Exit Sub
End If


'Turn off screen updating during next process

Application.ScreenUpdating = False

'Select worksheet to input new name and resort in alphabetical order

Sheet2.Select ' November sheet

ActiveSheet.Unprotect

'Select top name cell (D8), create blank row below with same formatting

Range("D8").Activate
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats


'Select blank name cell and insert new name

Range("D9").Activate
ActiveCell.Value = strName

' Copy formula down for new name entry

Range("Z8").End(xlToLeft).Select
Selection.Copy
Range("Z8").End(xlToLeft).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False





The VBA code then goes on to sort the names in alphabetical order. If it would help to have the full code please let me know.

[KayCee]

Please note that the images are in the wrong order. The lower image is the first image and the upper image is the result of running the VBA code. I also copied the code relating to the November sheet and not the Members sheet. The code for the Members sheet is very similar except for the selection of the Member worksheet.


Sheet27.Select ' Members sheet

ActiveSheet.Unprotect

'Select top name cell (B8), create blank row below with same formatting

Range("B8").Activate
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats

'Select blank name cell and insert new name

Range("B9").Activate
ActiveCell.Value = strName

' Copy formula down for new name entry

Range("Z8").End(xlToLeft).Select
Selection.Copy
Range("Z8").End(xlToLeft).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Sort name list in alphabetical order

Range("B8", Range("B8").End(xlDown).End(xlToRight)).Select
Selection.Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

[snb]

Always use Cell A1 in a worksheet.
Always use column A in a worksheet.
Always use row 1 in a worksheet.
Use an 'intelligent' table.

[KayCee]

Thanks for responding so quickly.

I am not anywhere near being an expert in VBA. However, even if I were to use A1 as a reference and then adjusted the off-sets accordingly I don't see how that would affect the copying down of the formulas from row 8 to the new blank row 9. Am I missing something obvious?

The range of names and values has been set up as a named table. Is that an "intelligent table?

[snb]

You didn't post a sample file.

[KayCee]

I posted a section of the VBA code above. I did not want to publish the Excel file because it contains names and financial information of members. You will not that I hid part of their names in the original jpg images. I could spend time anonomising the data in the Excel file if that would help.

[KayCee]

I have uploaded an anonomised version of the Excel spreadsheet in the hope that it helps. Could the problem be with the line of code that selects Row 8 to copy down the formula?

[KayCee]

Further information.
Even without using VBA, if I insert a row in the Excel worksheet Members table (select row > right-click > left-click insert) the rogue formula appears in D column which should remain blank as the cells above and below have no formula. Why??

[snb]

I ignore 'protected' workbooks/code

[KayCee]

Sorry, I don't understand you advice. However, I found a solution to the problem although I don't know why it occurred in the first place. I converted the table to a range and then designated the range into a table. That fixed the problem. I will mark this as SOLVED.