search and/or loop dilema

[nancy/karen]

After a week of trial and much error I am still without a solution. Could
someone help me? The macro I have posted below only works on the first row
of the "AB3:AL25" range.

Baiscally I would like it to accomplish the following:
pull info from the range "AB3:AL25" look for only a specific customer's info
that info is a numeric value greater than zero and located in column "AB"
rows 3 through 25

If "AB3" equals zero then the info in row 3 in columns "AB" through "AL"
should be ignored
If "AB3" is greater than zero then the info in row 3 "AB" through "AL"
should be copied to a new range destination starting at row 1 "AN" through
"AX"
then move to the next row repeat the search and copy to the next blank row
in the new range.

Sub Button12_Click()
Dim d1 As Variant
Dim h1 As Variant
Dim m1 As Variant

Dim i As Integer, c As Integer, k As Integer
c = 28
k = 1
i = 3
d1 = Sheets("daily production").Cells(i, c)
d1 = Sheets("daily production").Cells(i, c).Value

If d1 <> 0 Then
Sheets("daily production").Range("an1.ax1").Value = Sheets("daily
production").Range("ab3.al3").Value
h1 = Sheets("daily production").Range("ab3.al3").Value
h1 = Sheets("daily production").Cells(c, i + 1)
h1 = Sheets("daily production").Cells(c, i + 1).Value

m1 = Sheets("daily production").Range("an1.ax1")
m1 = Sheets("daily production").Cells(c + 10, k + 1)
m1 = Sheets("daily production").Cells(c + 10, k + 1).Value
m1 = h1

If d1 = 0 Then
d1 = Sheets("daily production").Cells(i + 1, c)
d1 = Sheets("daily production").Cells(i + 1, c).Value
h1 = ""
m1 = ""
For i = 3 To 25
Next i
i = i + 1
If m1 <> "" Then
k = k + 1
Do Until i = 25
Loop
End If
i = 25
End If
End If
End Sub

Help will be most appreciated!
N/K

 

[Joel]

Sub Button12_Click()

NewRow = 1

with Sheets("daily production")
For Rowcount = 3 To 25
d1 = .Range("AB" & RowCount)

If d1 <> 0 Then
.Range("AB" & RowCount & ":AL" & RowCount).Copy _
destination:=.Range("AN" & NewRow)
Newrow = NewRow + 1
End If
Next Rowcount
end with
End Sub

 

[nancy/karen]

Joel -

thank you for your help. I have entered and verified my entry with what
you sent.
I was not able to place the ) semi-colon between the word "destination"
and the (=) equal sign. The program did run without it. But, as written, it
did not preform according to my expectations. What it did do was highlight
row 14 as if to copy - that's it.

suggestions?

 

[Joel]

You must of lost the line continuation character after the copy on the
previous line. Look at my posted code.

 

[nancy/karen]

I deleted my entry, copied and pasted yours. I received run time error
"1004"

In debugging mode, it highlights the .copy _ line.
Am I missing an "add-in" or perhpas I have an older version of Excel?

 

[Joel]

----I tested the code and it is working. Here are some reasons you may have
a problem

1) You have more than one workbook open. Add Thisworkbook to specfy the
workbook with the macro

from
With Sheets("daily production")
to
With Thisworkbook.Sheets("daily production")

2) Make surre the code is in the correct VBA sheet. Since the code works
off a button it should be in the VBA sheet "daily production"


3) Make sure the sheet name "daily production" matches the sheet name on the
worksheet tab. Make surre no spaces are at the beginning and end on the tab
and don't put the double quotes in the tab

4) You have merged cells that you are copying or locked cells. Try deleting
columns AN: AX and try again.

This code is very simple and shouldn't fail.