I'm trying to make the button find the last instance of a non-empty cell in column B, then select the row between (B:L) from the last non-empty cell in column B. Copy the format and formula in the selection then insert a new row below and insert the format and formula. The reason I'm not using a table is that the tables are in weekly periods and I need to be able to add more to a week without having to manually move the other weeks down.
I know little about VBA so I'm having trouble.
I have:
Private Sub CommandButton1_Click()
' Go to last cell
Range("B19").Select
Selection.End(xlDown).Select
LastCell = [A65536].End(xlUp).Offset(-1, 0).Address
Range(LastCell).Select
' Enter new line
Selection.EntireRow.Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove <- This line is a problem.
Dim oCell As Range
While (oCell.Offset(-1, 0).Value = "") ' If the cell above is not empty
oCell.Offset(-1, 0).Copy Destination:=oCell ' Copy the formula from the cell above
Selection.Offset(-1, 0).Select ' Move one cell to the right
Wend
End Sub
Firstly it isn't finding the last non-empty cell in column B, rather I'm specifying a cell. Secondly it doesn't seem to enter a new line.
Can anyone help. Thanks
I know little about VBA so I'm having trouble.
I have:
Private Sub CommandButton1_Click()
' Go to last cell
Range("B19").Select
Selection.End(xlDown).Select
LastCell = [A65536].End(xlUp).Offset(-1, 0).Address
Range(LastCell).Select
' Enter new line
Selection.EntireRow.Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove <- This line is a problem.
Dim oCell As Range
While (oCell.Offset(-1, 0).Value = "") ' If the cell above is not empty
oCell.Offset(-1, 0).Copy Destination:=oCell ' Copy the formula from the cell above
Selection.Offset(-1, 0).Select ' Move one cell to the right
Wend
End Sub
Firstly it isn't finding the last non-empty cell in column B, rather I'm specifying a cell. Secondly it doesn't seem to enter a new line.
Can anyone help. Thanks