Help with tidying up code please

[Andy]

I have a table on a worksheet, there will be several of these tables
(one for each project) one after the other on the worksheet, the first
row contains the 'Name of Project' at Column A. In column D of the
first row is the Insert Row button.

Row 2 is headings for the table.

Row 3 is where the data starts.

The code works well , in that it uses
Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Select
to work out where the macro is being called from, it then stores name of
the project in strName.

The code then inserts a row at Row3 just below the headings, doing this
takes the formatting from the heading rows, so the code copies the row
below and pastes over the new row at Row 3.

Clears the contents and then inserts the date in the first cell of the
new row and the project name in the second cell of the new row.

It does all i want it to do, I was just wondering if their is any
tidying up i could do.

Also, whenever I use activecell.offset I always include Range("A1"), is
this necessary and what does it mean, from looking at vba help,
including Range("A1") means a ref to cell A1, but that does not seem to
be the case.

Any pointers you can give me on the code would be appreciated, always
like to learn new ways of coding something.

Sub InsertRow()

Dim myDate As Date
Dim MyStr As String
Dim strName As String

Application.ScreenUpdating = False

myDate = Date
MyStr = Format(myDate, "mmm yy")


Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Select

strName = ActiveCell.Offset(0, -4).Range("A1").Value

ActiveCell.Offset(2, 0).Range("A1").Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
Selection.ClearContents
With ActiveCell
.Value = myDate '(dd / mm / yyyy)
.Offset(0, 1) = strName
.Select
End With
End Sub

 

[Norman Jones]

Hi Andy,

Try:
'============>>
Sub InsertRow()
Dim myDate As Date
Dim MyStr As String
Dim strName As String
Dim rng As Range

Application.ScreenUpdating = False

myDate = Date
MyStr = Format(myDate, "mmm yy")

Set rng = Range(ActiveSheet.Buttons(Application.Caller). _
TopLeftCell.Address)

strName = rng.Offset(0, -4).Value
With rng
.Offset(2, 0).EntireRow.Insert
.Offset(3, 0).EntireRow.Copy
.Offset(2, 0).EntireRow.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
With .Offset(2).EntireRow.Cells(1)
.Value = myDate '(dd / mm / yyyy)
.Offset(0, 1) = strName
.Select
End With
End With

Application.ScreenUpdating = True

End Sub
'<<============

 

[Andy]

Cheers Norman, works great.

The With...End With really helps tidy things up.

So from looking at this am I right in thinking that everything from With
rng......End With happens based on the cell where the button is located
(rng)?

Thanks alot, works nicely and I also learnt from that.

Andy

 

[Norman Jones]

Hi Andy

So from looking at this am I right in thinking that everything from With
rng......End With happens based on the cell where the button is located
(rng)?

The short answer is "Yes!", the slightly longer response would be:

Making selections is rarely necessary and is usually very inefficient. So, I
set the rng variable to the buttons top left cell and used that as an anchor
point. Any range can be defined and manipulated from the anchor point.