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.

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


"thomsonpa" wrote:

> 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.

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:

> 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.

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"thomsonpa" <(E-Mail Removed)> wrote in message
news:3EB66B36-4481-4E5C-8403-(E-Mail Removed)...
>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.

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

"Stefi" wrote:

> 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:
>
> > 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.