Help with tidying up code please

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

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
'<<============


---
Regards,
Norman



"Andy" <[email protected]> wrote in message
news:[email protected]...
>
> 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

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 wrote:
> 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
> '<<============
>
>
> ---
> Regards,
> Norman
>
>
>
> "Andy" <[email protected]> wrote in message
> news:[email protected]...
>
>>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
>
>
>

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.


---
Regards,
Norman



"Andy" <[email protected]> wrote in message
news:%[email protected]...
>
> 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 wrote:
>> 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
>> '<<============
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Andy" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>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
>>
>>