Print labels for items received on receiving report

Marvin P:

I got the code here:

http://www.ozgrid.com/forum/showthre...t=52646&page=1

It just copies one row, then stops.

?

Lost

Hello!

"If there is an X (or anything) in cells A8:A19 on Sheet A, then copy the row (or rows) to the next available row on Sheet B."

I find plenty of code to copy the formulas, but I want to copy just the values. The copied-over IF formulas are giving #REF errors.

I'll keep Googling, but if anyone has this code handy, I would appreciate it.

VR/Lost


(This post relates to the workbook on this thread, plus I already had this same problem earlier in the thread, so I didn't want to double-post to a new thread.)

All,

How this works: Enter data in the columns on the first sheet, put an X in the A column, and hit a button to copy those selected rows over to the second sheet. From the second sheet, you can edit the values, then hit a button to put those values onto an Avery label.

Here's the question:

For any value of "H" (for on-hold) in the H column, when that row is copied to the Labels sheet, it needs to make a copy of the row, but with the H turned into an A and the Status (column M) turned into "ITEM ON HOLD".

(Select A,N,C, or NS, one tag prints. If H is selected, an H and an A tag need to print with the same information on them. But if the H is the tenth tag (not necessarily tag #10, then it won't copy it because H + A will exceed the 10 tag limit.)

Hope I explained that good enough. Workbook and Label template attached and should go into the same folder to work.

I appreciate the help!

Lost

All,

I am still working with this code:

Private Sub btnEditLabels_Click()
Dim ce As Range, NR As Long

   For Each ce In Range("A8:A" & Cells(Rows.Count, "A").End(xlUp).Row)
      If Not IsEmpty(ce) Then
        With Sheets("Labels")
            NR = .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Row
            If NR >= 12 Then
                MsgBox "You already have 10 labels prepared on the Label sheet."
            Else
                .Unprotect "SECRET"
                .Cells(NR, "A").Resize(1, 17).Value = Range(ce, ce.Offset(0, 16)).Value
                ce.ClearContents
                .Select
                .Protect "SECRET"
            End If
        End With
      End If
    Next ce
End Sub

How it should work:
a) Copy a row.
b) If it had an H in the H column, make a copy of the row just copied only with H turned into an A and the Status (column M value) turned into "ITEM ON HOLD".
c) Keep copying rows, adding an A row for any H's.
d) Stop copying if the row=12 (that will be ten labels for the Avery sheet).

I don't know how to handle if the tenth label to be copied over has an H. The program would add an A row (H's always get an A), but then that would make 11 labels.

I am wrapped around this, bigtime. Anyone have any idea how to make this work?



Respectfully,

Lost