Excel 2007 Autofilter to Unique Excel Templates 1

[BxWill]

thread707-1547015

Have a similar challenge whereby I am interested in using Autofilter to copy data to Excel Templates.

Reviewed the above thread and curious as to what modifications should be made to the code below if I want to filter the data to separate Excel Templates?

In other words, I have built 5 unique Excel Templates at C:\...\Microsoft Data... (with macros to generate pivot tables on the dynamic range in Sheet1) -one for each unique filtered value in my list (Widgets, Bolts,Blocks,Washers,Pipes).

Also, I currently receive a run-time error when running the code below that indicate that Excel cannot access the file 'C:\72BF8000'



Sub Testing()
'
' Testing Macro
'
Dim r As Range, ws As Worksheet, i As Integer
Set ws = ActiveSheet
i = 1
For Each r In

• 
  ws.[D1].AutoFilter _
  Field:=4, _
  Criteria1:=r.Value
  
  'r.CurrentRegion.Copy
  ws.[D1].CurrentRegion.Copy
  With Workbooks.Add
  With .Sheets(1)
  .Paste
  .UsedRange.EntireColumn.AutoFit
  .SaveAs "C:\FirstTest" & i & ".xls"
  End With
  .Close
  End With
  i = i + 1
  Next
  End Sub

 

[BxWill]

Actually, the current manual process is as follows;

Run sql server query to extract data for all 20 products

Save sql query results to Excel 2007 Worksheet

Filter sql query results (Excel table) using product as criteria

Select all visible cells (Ctrl * and then Ctrl C)

Open pre-existing MS Excel template for respective product (example Widgets.xltm)

Go to Sheet 1 of Excel template

Paste filtered results (Ctrl V)

Access footer and input "Date, as of 10.20.10" (Today's date) in the leftmost feeder

Then, input "Page 1 of 2 or Page 1 of 3,etc. in the rightmost feeder

Set the print area

Save as a Excel 2007 workbook (e.g. Widgets_10.20.10.xlsx)

Close Widgets_10.20.10.xlsx

Return to the main MS Excel Worksheet (that contains all of the data for all of the products and filter for the next product until all 20 workbooks are created.

Each time that I filter, I copy paste to the respective MS Excel template (1 template for each unique product) and then save as a Excel 2007 Workbook.

 

[SkipVought]

So is there a question?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

No question, Just outlining the manual process per your request.

 

[BxWill]

Is option 2 possible?

 

[SkipVought]

What do you mean by "save the filtered results to the first worksheet (sheet titled "data") in the respective macro-enabled Excel template"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

During the manual process, I would save the filtered data to the appropriate Excel template. The only reason why I save data to the Excel templates is due to the Excel templates containing formatting such as pivot tables, Headers, Footers, etc.

If I didn't paste the data into the Excel Template, I would have to re-create the pivots, Headers, Footers, etc. every time that I prepare the reports.

The use of a Excel template could be possibly eliminated if I could create the footers, headers and name each of the 20 workbooks with the unique product name using vba.

The current vba code allows for the 20 workbooks to be created but I have to open each one to determine what records for which product is contained.

Grateful for the insight received so far. It does save quite a bit of time in comparison with the manual process.

 

[SkipVought]

Each of your templates could query your workbook/sheet (table) to return the data for the appropriate criteral. Whenever the template workbooks open, this query could run.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Okay,

So, I can revert back to the option mentioned in the post on Oct 5th (at 18:57) such as below that has code in each of the 20 Excel templates.

Option ExplicitSub Copy_XS_Data()
Dim rng As Range
Dim wb As String
Dim MyDrive As String
wb = ThisWorkbook.Name
MyDrive = Worksheets(1).Range("D1")
'Open Workbook'
Workbooks.Open Filename:=MyDrive & "C:\All_Data.xls" 'Filters XS data'
With ActiveWorkbook.Worksheets(1) 'check Autofilter is on, turn on if not
If Not .AutoFilterMode Then
.Range("D1").AutoFilter
.Range("P1").CurrentRegion.AutoFilter Field:=2, Criteria1:="=*Widgets*"
If rng Is Nothing Then
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Workbooks(wb).Worksheets(1).Range("A1") End If
.ShowAllData
End With
End Sub


So, how would I "fire" all 20 Excel templates in sequence using Excel instead of opening each template and running the code?

 

[SkipVought]

Ok, you have 20 templates to send to 20 different recipients, and I suppose that the 20 recipients are not on the network or do not have macro priviledges, so they really cannot PULL the data in: you must PUSH the data out. Yes?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

When I drop the data into the 20 templates, I then save as 20 separate workbooks and distribute to just one person.

Would MS Excel still be the application of choice or should MS Access be considered if I want to create 20 unique workbooks in sequence instead of opening each template and "pulling in" data?

 

[SkipVought]

You may have explained this before, but ONE guy getting 20 workbook, that will grow to over 35? WHY? You are taking perfectly WHOLE data and chopping it up into pieces for WHAT? There is absolutely no added value to "the guy". If I were him, I sure would not want my mailbox and Documents folder clutterd up with several dozen pieces of similar stuff, when ONE WHOLE would suffice. This is a flawed process IMNSHO!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I hate to encourage this process, but.

1) build a list of full path to each of your 20 to 35+ wb, the criteria value(s)

2) loop thru this range, opening each wb, filtering the data according the the associated criteria value(s), copy & paste in the wb, save wb and close wb.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Appreciate the patience in resolution of this problem.


The person receiving the 20 workbooks is the Manager.

He then sends to 20 different individuals.

The full path for the 20 workbooks is P:\Monthly Reports\

Criteria for producing the 20 reports (1 for each product) every other week is simply

End_Date > Last day of month

(For example, "End_Date > 8/31/2010" for the reports produced on Aug 1st as well as Aug 21st)

I am not sure as to the coding to accomplish what you suggest.

Any further insight regarding coding is appreciated.

 

[SkipVought]

"Every other week" and "End_Date > Last day of month" are contradictory requirements. The latter is unintelligible.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

What was meant is that I produce the reports twice a month and the criteria is the same during any given month;

End Date (field within database) > Last day of the month.

 

[SkipVought]

I do not understand how the END DATE can be GREATER THAN the last day of month.

Your requirements must be intelligible, if you want help.

So if twice a month, what is the criteria for EACH of the dates?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Sorry for the confusion.

The END_DATE is really the Product_End_Date.

So, by specifying that the Product_End_Date is greater than the last day of the month, one is able to extract all products that have not expired.

 

[SkipVought]

What code are you starting with?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Code is similar to

Select Prod.Product_Name,
convert(varchar,Prod.Product_End_Date,101),
Mfg.Bin_Number,
Mfg.Manufacturer_Name,
convert(varchar,Prod.Product_Order_Date,101)
From Product Prod
Inner Join Manufacturer Mfg
on Prod.Product_ID = Mfg.Product_ID
Where Prod.Product_Name in ('Widgets', 'Bolts', 'Washers'...)
and Product_End_Date> '10/31/10'
Group By Prod.Product_Name