+ Reply to Thread
Results 1 to 4 of 4

Help with tidying up code please

  1. #1
    Andy
    Guest

    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

  2. #2
    Norman Jones
    Guest

    Re: Help with tidying up code please

    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" <andy_hammer@deleteyahoo.com.au> wrote in message
    news:eXnbhea5FHA.2816@tk2msftngp13.phx.gbl...
    >
    > 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




  3. #3
    Andy
    Guest

    Re: Help with tidying up code please


    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" <andy_hammer@deleteyahoo.com.au> wrote in message
    > news:eXnbhea5FHA.2816@tk2msftngp13.phx.gbl...
    >
    >>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

    >
    >
    >


  4. #4
    Norman Jones
    Guest

    Re: Help with tidying up code please

    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" <andy_hammer@deleteyahoo.com.au> wrote in message
    news:%23sphgHb5FHA.1716@TK2MSFTNGP10.phx.gbl...
    >
    > 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" <andy_hammer@deleteyahoo.com.au> wrote in message
    >> news:eXnbhea5FHA.2816@tk2msftngp13.phx.gbl...
    >>
    >>>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

    >>
    >>



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1