simple code but it doesn't look like usual vba code

[bpascal123]

Hi,

I'd like to copy the format and more importantly the formulae of the
last row and to insert it as a new row in the last of the current
range.

A1 contains a number in blue
B1 contains a date
C1 has a vlookup formulae based on the value in F1
D1 same as C1
E1 same as D1
F1 contains a number used as a reference for the C1, D1, E1 vlookup
formula
G1 contains a number
H1 contains a number
i1 contains the result of the product of G1 and H1

The sheet is protected, range C:F and i1 is locked.

As I can't insert a new row on a protected sheet, I have this workbook
macro :

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.Protect "sos", UserInterfaceOnly:=True
End With
End Sub

The following macro does the job but it can be made shorter and look
more like usual vba code.

Sub InsertRows()
Dim i, lastrow As Long
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
Rows(lastrow + 1).Insert
Rows(lastrow).Copy Destination:=Rows(lastrow + 1)

For i = 1 To 9
Cells(lastrow + 1, i).Value = ""
Next i

For i = 1 To 9
Cells(lastrow + 1, i).Formula = Cells(lastrow, i).Formula
Next i

Cells(lastrow + 1, "a").Value = ""
Cells(lastrow + 1, "b").Value = ""
Cells(lastrow, "C").Copy Destination:=Cells(lastrow + 1, "C")
Cells(lastrow, "D").Copy Destination:=Cells(lastrow + 1, "D")
Cells(lastrow, "E").Copy Destination:=Cells(lastrow + 1, "E")
Cells(lastrow + 1, "f").Value = ""
Cells(lastrow + 1, "g").Value = ""
Cells(lastrow + 1, "h").Value = ""
Cells(lastrow, "i").Copy Destination:=Cells(lastrow + 1, "i")

End Sub


Any suggestion?

Pascal

 

[Vacuum Sealed]

Hi Pascal

Maybe.
Sub CopyDown()

Range("C1:E1").Select
Selection.Copy

Columns("C").Find("", Cells(Rows.Count, "C"), xlValues, _
xlWhole, , xlNext).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("i1").Select
Selection.Copy

Columns("i").Find("", Cells(Rows.Count, "i"), xlValues, _
xlWhole, , xlNext).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub


The Guru's will most likely have something cleaner and faster than this, so
hang out for a bit, also, the only negative using the .Find is that if there
are any blank cells along any of the columns selected it will insert the
value there instead of at the bottom of the sheet...

I use this all the time, all I do is put an indescriminate character (
usually white ) in the blank cells and it skips straight over it and goes
right where I want it to go.

HTH
Mick.

 

[bpascal123]

Hi,

The row copy destination method would do fine if the vlookup and
product formula move what it refers to (the lookup value for vlookup
and the operand for the multiplication), like using copy-paste within
Excel. If there isn't a dollar sign in the formula, everything should
move along.

So maybe someone has an idea for a copy destination that does the same
job as in Excel because it would make the code quite shorter.

Thanks,
Pascal