-1

My process begins by opening "ExcelReport", https://app.box.com/s/2ieux0ws9c07k1d5x7rb, which is my source data. Then I open the file I've been working on "jmmac2", https://app.box.com/s/5sd1j11hjp9in7qc57os I hit the Import button to get the source data into "jmmac2", then hit the Export button to create invoices. What it's doing now is executing through the filter line (the line just after Dim LastRow2), but rather than filtering any of the data, it hides all rows so the next line (Set rngUniques...) returns nothing.

Ideally "Export" will populate a series of worksheets, each named by the client name_invoice #, and the subsequent data will be dropped into each invoice. I have a blank invoice template as Sheet2 that is used to start each new invoice. The invoices each have a button that will transfer the information into a new Outlook message, then color the tab green to signify it has been sent. All other macros in this workbook are working perfectly, can anyone help me solve the problem above? It is in the "CopyData" macro.

Peter
  • 2,551
  • 1
  • 14
  • 20
Jason
  • 5
  • 1
  • 3

2 Answers2

0

If I delete the named range "Criteria" from the "Convert" sheet and change this:

 Sheets("Convert").Range("K1:K" & LastRow).AdvancedFilter _
   Action:=xlFilterInPlace, CriteriaRange:=Range("K2:K" & LastRow), Unique:=True

to this:

Sheets("Convert").Range("K1:K" & LastRow).AdvancedFilter _
                      Action:=xlFilterInPlace, Unique:=True

then it works for me.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • It still isn't working even after taking that bit out. I get a "no cells were found" error, and when I go to debug, this line is highlighted: Set rngUniques = Sheets("Convert").Range("K2:K" & LastRow).SpecialCells(xlCellTypeVisible) – Jason Aug 04 '14 at 23:06
  • And you also deleted the named range from the sheet? – Tim Williams Aug 04 '14 at 23:54
  • None of the ranges are named – Jason Aug 05 '14 at 00:06
  • Can you upload the current version with your changes? – Tim Williams Aug 05 '14 at 00:09
  • https://app.box.com/s/h3z1lbeamx5kuw021g95 - you'll need to have both files open, then hit the Import button on jmmac3 to bring the data in. – Jason Aug 05 '14 at 00:26
  • You may not have named any ranges yourself, but did you actually check the sheet? Using Formula tab >> Name Manager on the "Convert" sheet there is clearly a name "Criteria" (ok, not a named range, but a Name nonetheless). Delete that! – Tim Williams Aug 05 '14 at 05:53
0

I believe the range selection on line 12 of CopyData just needs to be "K2:K" & LastRow instead of "K1:K" & LastRow.

Try changing this:

Sheets("Convert").Range("K1:K" & LastRow).AdvancedFilter _
   Action:=xlFilterInPlace, CriteriaRange:=Range("K2:K" & LastRow), Unique:=True

To this:

Sheets("Convert").Range("K2:K" & LastRow).AdvancedFilter _
   Action:=xlFilterInPlace, CriteriaRange:=Range("K2:K" & LastRow), Unique:=True
Seth McCauley
  • 983
  • 11
  • 24