Copying a row of cells to the next open row 1

[bubarooni]

I have an expense report that I am trying to set up.

Row 5 of the sheet is where a user enters their info for vendor, date, amount, etc. It consists of several blank cells and a few list boxes.

What I would like to do is have the user enter their expense line in Row 5 and click a button. The button would copy row 5 from a5 to j5 into row seven.

The user would then enter their next expense line into row 5 again and click the button and the data in row 5 be copied into the next open row, in this case row 8. The next expense line into row 9, etc, etc.

How do I make it copy into the first open row?

Thanks In Advance!

 

[SkipVought]

Hi,

What code do you have so far?

Skip,

_[red][/red]

 

[SkipVought]

You can try this, assuming that you data begins in A5...

Sub Copy2NextEmptyRow()
    With ActiveSheet
        .Range(.[A5], .[A5].End(xlToRight)).Copy .Cells(.[A5].End(xlDown).Row + 1, 1)
    End With
End Sub

Skip,

_[red][/red]

 

[bubarooni]

Well, that at least is doing something!

It does throw a run time error '1004'
Application defined or object defined error

It stops on this line:
.Range(.[A5], .[A5].End(xlToRight)).Copy .Cells(.[A5].End(xlDown).Row + 1, 1)

So is it just something to do with the cells being copied? It's not those combo boxes is it?

 

[SkipVought]

Describe your sheet.

Exactly where is your data entered on row 5?

It ran without error in my testing.

Skip,

_[red][/red]

 

[bubarooni]

A5, B5, C5, D5, F5 are empty cells where a user enters info directly in the sheet.

H5 is a simple formula (=D5*F5).

E5 and G5 are Form combo boxes.

I5 uses vlookup to obtain a value from the G5 combo box.
J5 uses vlookup to obtain a value from the E5 combo box.

Each combo box info is contained on a seperate worksheet (Control_Info) in it's own three column named range.

The 'Add Row' button is a Control Toolbox command button.

I tried with just blank cells in row 5, but got the same error. I think that means I'm really messing this up!

 

[SkipVought]

OK, the controls will cause some problems with the current code.

By the way, this assumes that row 5 has a value in each column.

Sub Copy2NextEmptyRow()
    Dim r As Range, lNextRow As Long
    With ActiveSheet
        lNextRow = .Cells(.[A5].End(xlDown).Row + 1, 1).Row
        For Each r In .Range(.[A5], .[A5].End(xlToRight))
            .Cells(lNextRow, r.Column).Value = r.Value
        Next
    End With
End Sub

Skip,

_[red][/red]

 

[bubarooni]

I still get the same error, but I got this code to work:

     If IsEmpty(Range("a7")) Then
        Range("DATA").Copy Destination:=Range("A7")
     ElseIf IsEmpty(Range("a8")) Then
        Range("DATA").Copy Destination:=Range("A8")
     End If

I made a5-j5 a named range that I called DATA. I suppose I could just do about 30 ElseIf's and get it to work the way I want except, just as you alluded to earlier it copies the two combo boxes and the vlookup statements instead of the values.

Is there a way to programmatically copy just the values in the cells?

 

[PHV]

A starting point:
Range("DATA").Copy
Range("A7").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
Application.CutCopyMode = False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bubarooni]

I'm using this right now. Had to add a range for each destination row though.

     If IsEmpty(Range("a7")) Then
        Range("DATA1").Value = Range("DATA").Value
     ElseIf IsEmpty(Range("a8")) Then
        Range("DATA2").Value = Range("DATA").Value
     End If

At least it is not copying the darned combo box controls...

 

[PHV]

Had to add a range for each destination row though
What about this ?
Dim r As Long
r = 7
Do Until IsEmpty(Range("A" & r))
r = r + 1
Loop
Range(Cells(r, 1), Cells(r, Range("DATA").Columns.Count)).Value = Range("DATA").Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bubarooni]

Uh, that works perfect...

i'm slightly speechless at the moment...

 

[bubarooni]

OK, I have pretty well recovered now.

I have one last question. Can I modify this line:

Range(Cells(r, 1), Cells(r, Range("DATA").Columns.Count)).Value = Range("DATA").Value

To something like this:

Range(Cells(r, 5), Cells(r, Range("E" & r).Columns.Count)).Value = Application.WorksheetFunction.VLOOKUP('Control Info'!$F$21, Purpose, 2)

And place it right below your code so that it displays in column E of the appropriate row a vlookup value for a combo box that is in cell 5E.

F21 is where the combo box's selected index is stored
on the Control Info sheet. The main sheet that references Control Info is called Expenses.

the values for the combo box are stored in a range named purpose on that same sheet, Control Info.

I use vlookup successfully as a formula in a cell like this

=VLOOKUP('Control Info'!$F$21, Purpose, 3)

but can't get it to work at all in VBA. I have seen some examples here but the syntax has me totally confused.

I think if I place that modified line directly below yours it would process correctly.

Anyway, THANKS FOR YOUR HELP!!!!!

 

[PHV]

Perhaps this ?
Range("E" & r).Value = Application.WorksheetFunction.VLOOKUP(Sheets("Control Info").Range("$F$21"), Sheets("Control Info").Range("Purpose"), 2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bubarooni]

Helps? That works perfectly!!!!

How do you give a supernova instead of a star on this board?