Find next empty row (Excel 2000)

Topic

New Reply

I have a spreadsheet on which payments are calculated. Every time before I calculate a new payment I copy the previously calculated payment into a list of previous payments. How can I have a macro copy the payment information into the next empty row in the list.

Any help will be appreciated.

Kobus

Reply | Quote

Replies

(Edited by sdckapr on 27-Apr-04 14:42. Cleaned up the code, to eliminate all the variables)

We need more specifics on what you need. Could you provide some more details?

Are you always moving a particular cell in a worksheet into the next free row in a given column of a particular worksheet?

This code (for example) will copy the value from Sheet1!A1 to the next available row in Col A of Sheet2 (change sheet names, starting cell and column as appropriate in the code.

Option Explicit
Sub CopyCalc()
    Dim wksSource As Worksheet
    Dim rSource As Range
    Dim wksDest As Worksheet
    Dim iColDest As Integer

'Change these as desired    
    Set wksSource = Worksheets("Sheet1")
    Set rSource = wksSource.Range("A1")
    Set wksDest = Worksheets("Sheet2")
    iColDest = 1
    
    wksDest.Cells(65536, iColDest).End(xlUp).Offset(1, 0).Value = _
        rSource.Value
    
    Set rSource = Nothing
    Set wksSource = Nothing
    Set wksDest = Nothing
End Sub

Steve
PS. Though you could combine all into one line and use (again change as desired).

Sub CopyCalc()
    Worksheets("Sheet2").Cells(65536, 1).End(xlUp).Offset(1, 0).Value = _
        Worksheets("Sheet1").Range("A1").value
End Sub

Reply | Quote

Steve

Thank you very much, you always make things look so easy. I have used your code as an outline and adapt it to suit my workbook. I actually copy all the information in the row to the new location, so a gave the range a name and then Special Paste the values into the new location.

Thank you again for your time.

Kobus

Reply | Quote

Steve

Thank you very much, you always make things look so easy. I have used your code as an outline and adapt it to suit my workbook. I actually copy all the information in the row to the new location, so a gave the range a name and then Special Paste the values into the new location.

Thank you again for your time.

Kobus

Reply | Quote

(Edited by sdckapr on 27-Apr-04 14:42. Cleaned up the code, to eliminate all the variables)

We need more specifics on what you need. Could you provide some more details?

Are you always moving a particular cell in a worksheet into the next free row in a given column of a particular worksheet?

This code (for example) will copy the value from Sheet1!A1 to the next available row in Col A of Sheet2 (change sheet names, starting cell and column as appropriate in the code.

Option Explicit
Sub CopyCalc()
    Dim wksSource As Worksheet
    Dim rSource As Range
    Dim wksDest As Worksheet
    Dim iColDest As Integer

'Change these as desired    
    Set wksSource = Worksheets("Sheet1")
    Set rSource = wksSource.Range("A1")
    Set wksDest = Worksheets("Sheet2")
    iColDest = 1
    
    wksDest.Cells(65536, iColDest).End(xlUp).Offset(1, 0).Value = _
        rSource.Value
    
    Set rSource = Nothing
    Set wksSource = Nothing
    Set wksDest = Nothing
End Sub

Steve
PS. Though you could combine all into one line and use (again change as desired).

Sub CopyCalc()
    Worksheets("Sheet2").Cells(65536, 1).End(xlUp).Offset(1, 0).Value = _
        Worksheets("Sheet1").Range("A1").value
End Sub

Reply | Quote