+ Reply to Thread
Results 1 to 5 of 5

need help in writing simple macro

  1. #1
    Registered User
    Join Date
    04-29-2005
    Posts
    16

    need help in writing simple macro

    hello gurus!

    i have a simple excel sheet containing sales data. each row has 3 columns- product cost, number sold and a total currency value column(col 1 & 2 multiplied). at the end of the report is a summation field of column 3, showing grand total sales.

    now, i want to write a macro that will insert 5 lines at the end of the report (immediately after the last sale line item entry and before the grand total row.

    now, since the last row of the report will keep changing, how can i automate the process? can i save a global variable that will maintain how many data rows have been entered? i tried recording a macro but the position of the insertion becomes static then.

    please guide!

  2. #2
    Forum Contributor
    Join Date
    05-04-2005
    Posts
    136
    create a macro with the following code:

    Sub InsertRows()

    '
    Range("A3").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.EntireRow.Select
    Dim i As Integer
    i = 0
    Do
    ActiveCell.EntireRow.Select
    Selection.Insert Shift:=xlDown
    i = i + 1
    Loop While i < 5
    End Sub


    Quote Originally Posted by rkg
    hello gurus!

    i have a simple excel sheet containing sales data. each row has 3 columns- product cost, number sold and a total currency value column(col 1 & 2 multiplied). at the end of the report is a summation field of column 3, showing grand total sales.

    now, i want to write a macro that will insert 5 lines at the end of the report (immediately after the last sale line item entry and before the grand total row.

    now, since the last row of the report will keep changing, how can i automate the process? can i save a global variable that will maintain how many data rows have been entered? i tried recording a macro but the position of the insertion becomes static then.

    please guide!

  3. #3
    Registered User
    Join Date
    04-29-2005
    Posts
    16
    thank you for ur response tkaplan.

    this macro creates the 5 rows but it doesnt copy the forumla of third column into the 3rd column of the newly inserted rows. 3rd column has the product of col 1 & col 2.

    how can i copy the formula in the 3rd col of 5 new rows as well?

    thank u!

  4. #4
    Forum Contributor
    Join Date
    05-04-2005
    Posts
    136
    change the macro to:

    Sub InsertRows()

    '
    Range("C1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select

    Dim i As Integer
    i = 0
    Do
    ActiveCell.EntireRow.Select
    Selection.Insert Shift:=xlDown
    i = i + 1
    Loop While i < 5

    i = 0
    Do
    Range("c1").Select
    Selection.End(xlDown).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
    i = i + 1
    Loop While i < 5

    End Sub

  5. #5
    Registered User
    Join Date
    04-29-2005
    Posts
    16
    thank you tkaplan! this works perfectly!

+ 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