New Row

Hi. This is the macro that I currently have. As you can see it starts w/ rows(21:21), however, if row 21 is enter and you run the macro again, it starts again at row 21 hence copying down whatever is currently in row 21 to 22. I do not want this macro to do that. i want it to start at the next empty row if previous rows are occupied. how do i do that? Thanks.

Sub NewJob()
'
' NewJob Macro
' To insert new jobs.
'

'
    Rows("21:21").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("I20").Select
    Selection.Copy
    Range("I21").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("K20").Select
    Selection.Copy
    Range("K21").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("O20").Select
    Selection.Copy
    Range("O21").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A26").Select
End Sub

Dim Rws As Long


Rws = Cells(Rows.Count, "A").End(xlUp).Row

Cells(Rws, 1).Select

This selects the first empty cell after the last entry in column A

Change the column letter and column number to suite your requirements

Hello Ruckuz,

This version of the macro will copy the values from the previous row to the next empty row.

Sub NewJob()
'
' NewJob Macro
' To insert new jobs.
'

'http://www.excelforum.com/excel-programming/758088-new-row.html

  Dim RngEnd As Range
  
    Set RngEnd = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False)
    If RngEnd Is Nothing Then Exit Sub Else Set RngEnd = RngEnd.EntireRow.Offset(1, 0)
    
    With RngEnd
      .Cells(1, "I") = .Cells(1, "I").Offset(-1, 0)
      .Cells(1, "K") = .Cells(1, "K").Offset(-1, 0)
      .Cells(1, "O") = .Cells(1, "O").Offset(-1, 0)
    End With
       
End Sub

You didn't attach a sample workbook, so I don't know what you're up to.

You seem to be saying that you want the macro to copy certain data from row 20 to the next available row on the sheet every time the macro is run.

davesexcel's line:

Rws = Cells(Rows.Count, "A").End(xlUp).Row

loads the number of the last used row into Rws; the next available blank row is just Rws +1.
Then you need to copy the data values from row 20 to that row:

      Cells(Rws, "I").Value = Cells(20, "I").Value
      Cells(Rws, "K").Value= Cells(20, "K").Value
      Cells(Rws, "O").Value = Cells(20, "O").Value

So that the macro becomes:

Option Explicit
Sub insert()
    Dim Rws As Long
    
    'get next row
    Rws = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    'copy data
    Cells(Rws, "I").Value = Cells(20, "I").Value
    Cells(Rws, "K").Value = Cells(20, "K").Value
    Cells(Rws, "O").Value = Cells(20, "O").Value
End Sub

Though I'm curious why, if data in row 20 is changing, it just can't be put into the next row to begin with?

Hello ruckuz,

Now that I can see the layout, it is clear what you want to do.

Hello ruckuz,

I don't have Excel 2007 but I have a converter to read the 2007 format. Sometimes I can change the files and save them and the changes work. In this case, it isn't working.

Hello ruckuz,

Not a problem. You're new here. I don't expect you to have read all the rues about posting yet. I am still working on the code and just about have it working. I copied the data over to a 2003 workbook so I could tell the difference between my code problems and those raised by converting the workbook.

Hello Ruckuz,

Here is the working macro. Copy this into Module2. You can then add a button to the worksheet (somewhere) to run the macro or do it manually.

EDIT: I attached the workbook with the macro and the button.

Sub InsertNewRow()

  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("Jobs")
    
    Set Rng = Range("A3:W3")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column + 1).End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Sub
    
      Set RngEnd = RngEnd.Offset(0, -1).Resize(1, Rng.Columns.Count)
      RngEnd.insert Shift:=xlShiftDown
      
        For Each Cell In RngEnd.Offset(-2, 0)
          If Not Cell.Formula Like "=*" Then
             Cell.Offset(1, 0).Value = ""
          Else
             Cell.Resize(RowSize:=2).FillDown
          End If
        Next Cell
    
End Sub

Hello Ruckuz,

Happy New Year !

Hello Ruckuz,

Thanks for pointing that out. I will work on it.

Hello Ruckuz,

I replaced the Subtotal function with a Sum since that is what the Subtotal was doing. The ranges are now automatically sized and summed. The Subtotal functions in row 23 are still there. Here is the formula I used and a copy of the workbook.

Row 21:
=SUM(OFFSET(C$3,0,0,ROW()-3, 1))