copying if value in cell from range of cells

[thomsonpa]

I am looking vor the visual basic code to perform the following action.
I have column "a" with data in every cell, columns "j, k and l 'may have
data in them.

If there is data in j, k or l I want to copy the data from "a" into another
worksheet in column "a", then the data in j, k or l in either b, c or d on
the other worksheet on the same row number as the information in column a.
Since column a has over 120 rows I am looking for a simple solution to this.
Any help in pointing me in the right direction would be helpful.

 

[Joel]

Sub movedata()

RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & RowCount) <> ""
If .Range("J" & RowCount) <> "" Or _
.Range("K" & RowCount) <> "" Or _
.Range("L" & RowCount) <> "" Then

ColA = .Range("A" & RowCount)
ColJ = .Range("J" & RowCount)
ColK = .Range("K" & RowCount)
ColL = .Range("L" & RowCount)

With Sheets("Sheet2")
.Range("A" & RowCount) = ColA
.Range("J" & RowCount) = ColJ
.Range("K" & RowCount) = ColK
.Range("L" & RowCount) = ColL

End With

End If
RowCount = RowCount + 1
Loop
End With

End Sub

 

[Stefi]

Try this:

Sub test()
hitcounter = 1
endA = Range("A" & Rows.Count).End(xlUp).Row
For Each acell In Range("A2:A" & endA)
If Not IsEmpty(Range("J" & acell.Row)) Or _
Not IsEmpty(Range("K" & acell.Row)) Or _
Not IsEmpty(Range("L" & acell.Row)) Then
hitcounter = hitcounter + 1
Worksheets("othersheet").Range("A" & hitcounter).Value =
acell.Value
Worksheets("othersheet").Range("B" & hitcounter).Value =
Range("J" & acell.Row).Value
Worksheets("othersheet").Range("C" & hitcounter).Value =
Range("K" & acell.Row).Value
Worksheets("othersheet").Range("D" & hitcounter).Value =
Range("L" & acell.Row).Value
End If
Next acell
End Sub

Regards,
Stefi

„thomsonpa†ezt írta:

 

[Don Guillett]

Sub copyrangeif()'Execute from Source sheet
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
With Sheets("yourdestinationsheetname")
lr = .Cells(Rows.Count, "a").End(xlUp).Row + 1
'MsgBox lr
If Len(Trim(Cells(i, "a"))) > 0 _
And Application.CountA(Range(Cells(i, "j"), Cells(i, "l"))) > 0 Then
'MsgBox i
Cells(i, "a").Copy .Cells(lr, "a")
Range(Cells(i, "j"), Cells(i, "l")).Copy .Cells(lr, "b")
End If
End With
Next i
End Sub

 

[thomsonpa]

Many thanks, works perfectly. What would I do if it wasnt for this community.