Try Again... copy a row if another worksheet cell <>null

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Suzanne - 07 Dec 2007 23:00 GMT

I need to copy a row of formulas in a worksheet (Report), but only if there
is a value in another worksheet (Personnel). Right now I'm stuck at having
500 rows containing formulas like the following:

Report Last Name: 'Report'!F2 = IF('Personnel'!$A2<>"",'Personnel'!$A2,"")

and so on for first name (B2), Bldg. (C2), Room (D2), etc.

Worksheet: "Personnel" contains
A:A = Last Name
B:B = First Name
C:C = Bldg. (and so on)

Worksheet: "Report" contains
F2 = Last Name
G2 = First Name
H2 = Bldg. (and so on)

(plus 20 other columns of data, many of which have data validation drop-down
menus)

I also need to be able to add, delete, copy, paste records in 'Report'
(after all the Personnel data is there... I'm thinking this can get resolved
by coverting the formulas into values)

Once the 'Report' data is finished, the information must then go to
'ReportMerge' (a sorted, locked worksheet that Word will use for merge
reports); this worksheet also draws data in from yet another worksheet that
is only needed for the Word merge (and therefore not needed on 'Personnel'
and 'Report')

I've tried several solutions in this DG, but have not been able to get them
to work.

Suzanne

Reply to this Message

Otto Moehrbach - 08 Dec 2007 15:32 GMT

Suzanne
I'll try to help you with what you want to do. But let's take it one
step at a time. You say:
"I need to copy a row of formulas in a worksheet (Report), but only if there
is a value in another worksheet (Personnel)."
You don't say where you want to paste the row you copied. Also, you don't
say anything about what row you want to copy. I'll assume you want to copy
the last row in Column A and the row contents consist of 10 columns (Column
J), and you want to paste these 10 cells to the next row, and the cell in
question in the Personnel sheet is A1, and the active sheet is the Reports
sheet. Something like the following will do that.
Sub CopyLastRow()
Dim LastCell As Range
Set LastCell = Range("A" & Rows.Count).End(xlUp)
If Not IsEmpty(Sheets("Personnel").Range("A1").Value) Then _
LastCell.Resize(, 10).Copy LastCell.Offset(1)
End Sub
I realize this is just one small piece of what you want, but digest this
first. Then post back and tell me the next thing you want. Also tell me if
you want to tie the next thing in with the above code. HTH Otto

>I need to copy a row of formulas in a worksheet (Report), but only if there
> is a value in another worksheet (Personnel). Right now I'm stuck at
[quoted text clipped - 37 lines]
>
> Suzanne

Reply to this Message