Copy row based on text in original form

Question

I have been searching for an answer to this for a while and have gotten very close.

In short I have a form which contains various columns. One of those being a quantity. This column would always have a value as if no quantity it will stay blank. However if the cell is populated with a value I then need to copy the entire row of formula to the row beneath.

I came across the following on here this morning which is the closest I have come yet, but I don't understand enough of the code to make it fit my needs (or perhaps it isn't even the best method?)

Code I found:

Sub SmartCopy()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim N As Long, i As Long, j As Long
    Set s1 = Sheets("Order")
    Set s2 = Sheets("Converted")
    N = s1.Cells(Rows.Count, "C").End(xlUp).Row
    j = 1
    For i = 1 To N
        If s1.Cells(i, "C").Value = "" Then
        Else
            s1.Cells(i, "C").EntireRow.Copy s2.Cells(j, l)
            j = j + 1
        End If
    Next i
End Sub

On my form the quantity starts at E15 (with the parts being ordered being in the columns before hand). If E15 has a value then the formula should copy to the next row and so on. If the next row quantity is blank then I need to remove any formula entered. So in essence only filling down if there is a value in the quantity on the original sheet as there is one row to be put at the end too. I can do the final part, but any code I've found previously enters formula, so of course the cell is then not blank and causing other problems, hence asking if it can be done this way instead?.

This is a copy of the form. You will note the values start at A15 for the part No's, and the whole row of info would be needed if anything is ordered on the next row.

This is the converted version, as you see it populates through, but I need to copy down as described.

The rest of my code works, it is just this section I am having issues with. Current full code -

Sub ButtonMacro()
'Hide alerts
  Application.DisplayAlerts = False

'Add new sheet for table
  Sheets.Add.Name = "Converted"

'Populate new worksheet
    Range("A1").Formula = "MSG"
    Range("B1").Formula = "=Order!F2"
    Range("C1").Formula = "ORDER"
    Range("D1").Formula = "1400008000"
    Range("E1").Formula = "501346009175"
    Range("F1").Formula = "=TODAY()"
    Range("F1").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G1").Formula = "=Now()"
    Range("G1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Range("A2").Formula = "HDR"
    Range("B2").Formula = "C"
    Range("C2").Formula = "1400011281"
    Range("G2").Formula = "=Order!F2"
    Range("H2").Formula = "=Order!D2"
    Range("K2").Formula = "STD"
    Range("L2").Formula = "=Order!F5"
    Range("N2").Formula = "=Order!F7"
    Range("O2").Formula = "=Order!F8"
    Range("Q2").Formula = "=Order!F9"
    Range("R2").Formula = "=Order!F12"
    Range("A3").Formula = "POS"
    Range("B3").Formula = "=ROW()*10-20"
    Range("B3").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C3").Formula = "=Order!C15"
    Range("D3").Formula = "=Order!A15"
    Range("E3").Formula = "=Order!B15"
    Range("F3").Formula = "=Order!E15"
    Range("G3").Formula = "=Order!G15"
    Range("H3").Formula = "=IF(Order!H15<1,"""",""GBP"")"
    Range("M3").Formula = "=COUNTIF(C[-3], ""POS"")+COUNTIF(C[-3], ""HDR"")"

'Fills column to last row of data from Cell C3
    Dim LastRow As Long
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("C3:H3" & LastRow).FillDown

'Find last blank in A and fill with TRA
    Cells(Application.Evaluate("MAX(IF(C3:C2000<>"""",ROW(c3:c2000)),0,1)"), "A").Select
    ActiveCell = "TRA"

'Format cells to remove 0 value
    Range("A1:Z1000").Select
    Range("Z1000").Activate
    Selection.NumberFormat = "#;#;"

'Reinstate alerts
Application.DisplayAlerts = True
MsgBox "We have saved a copy of your order form to your computer at C:\X\X"
End Sub

The section "'Fills column to last row of data from Cell C3" is what currently doesn't do what I need.

Edit: I should also add, any number of parts could be ordered, so I can't specify row to row, it could be one part, it could be 1000.

Examples:
Client form filled out ready to be converted

Converted form (the yellow is what I'm trying to get rid of)

So in the converted version you will see TRA on row 33. This is because the current method is copying down and looking for the next blank cell, as they have formula in they aren't blank hence asking if it can be done this way instead. I hope that helps?

@oxwilder, that is correct. so they order 1 part, when this converts they get the normal first lines, then their line of requested parts, then the post parts line. If they ordered 15, you get the usual lines then 15 lines of part numbers, followed by the summary line. I looked at looping this but couldn't get it to work, and no one was able to answer the question. So, if they order 2 parts as example, how do I get it to loop through and copy down only if populated?. As I am not overly knowledgeable in this stuff without an example it doesn't mean much (sorry, that's me, not your explanation). — , Oct 09 '18 at 12:28
@oxwilder, added to original post as requested. Both client form and converted version. — , Oct 09 '18 at 13:05

score 0 · Answer 1 · answered Oct 09 '18 at 20:17

There's a lot going on here, and I'd first recommend paring down every reference to "select" and "activate" you can. Check here for some excellent information on that.

Your code adds a named worksheet, but then nearly everything following refers to a range that is not in the worksheet you just created. You'll need to name the worksheet specifically, and here it would help to use a with block. In your case:

'Add new sheet for table
  Sheets.Add.Name = "Converted"
With worksheets("Converted")
'Populate new worksheet
    .Range("A1") = "MSG"
    .Range("B1").formula = "=Order!F2"
    .Range("C1") = "ORDER"
    .Range("D1") = "1400008000"
    .Range("E1") = "501346009175"
End with

(Note the dots in front of .range)
This is assuming you only have one workbook open. If not, specify that too! Workbooks("Orders.xlsx").Worksheets("Converted").range("A1")
The non-specific ranges are likely what's causing the several blank rows on the current sheet--the compiler is counting rows on one sheet, storing that row number in memory, selecting some other sheet based on the .activate, and creating a value in that sheet's row.

If you want to find the last row in the "A" column that has a non-blank value in it, use lastrow = Worksheets("Converted").Cells(.rows.Count, "A").End(xlUp).Row

Good luck!

Greatly appreciate the answer, but doesn't quite fit the remit. The section regarding the use of select is brilliant so thanks for the link. The issue with finding the last cell is that the fill down fills the cells with formula, so that is why I get the big gap. How would I go about ignoring formula? My understanding is that isn't possible as formula or not it is still an entry in that cell. Is this correct?. — , Oct 10 '18 at 14:59

Copy row based on text in original form

1 Answers1