I tried your latest autofill code and the results are still the same... it's
not working. (At this point, I wouldn't mind if I had to settle with a
workaround.)

For your enable.outlining code, to test it I had to temporarily use this
back :
Selection.AutoFill _
Destination:= _
NewTempl.Range("A23:BT27"), Type:=xlFillDefault

But when I applied this & ran the macro:
NewTempl1.Protect "12345678", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

NewTempl1.EnableOutlining = True

I get an error stating "Runtime error 1004. Application-defined or
object-defined error"......

I have to say I'm sorry for this frustrating problems.....

"Joel" wrote:

Enable outlining is not a protection method. It is its own method.

from
NewTempl1.Protect "12345678", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
EnableOutlining = True

to
NewTempl1.Protect "12345678", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

NewTempl1.EnableOutlining = True

The only thing I can think of to fix the auto fill is the following:

FillRange = "A23:BT" & (23 + Prod_count - 1)
NewTempl.Range("A23:BT23").Select
Selection.AutoFill _
Destination:= _
NewTempl.Range(FillRange), _
Type:=xlFillDefault


"Mikaela" wrote:

I agree with you. I tried it again, but the results are strangely the same :-(

I'm not sure if EnableOutlining is a method. But I have an example of it
working. There's a macro in Template.xlt with EnableOutlining already working
& it will allow a user to group & ungroup columns in the template sheet
(PRODUCT TEMPLATE) while the sheet is protected :

Private Sub Workbook_open()
With Worksheets("PRODUCT TEMPLATE")
.Protect Password:="12345678", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

The above macro doesn't work in the new workbooks created with Macro 9 even
tho' the macro exists in the new workbooks. Maybe because in Macro 9 macro we
specified the new workbook's protection using

NewTempl1.Protect "12345678", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

I just don't know how to incorporate into Macro 9 so that all the new
workbooks will have the behavior of allowing the protected template sheet to
group/ungroup

TIA

"Joel" wrote:

Protect only has these methods. Not sure which one alows outlining.

expression.Protect(Password, DrawingObjects, Contents, Scenarios,
UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns,
AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows,
AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows,
AllowSorting, AllowFiltering, AllowUsingPivotTables)

Try one more time the fixed code I gave you (see below)

NewTempl.Range("A23:BT23").Select
Selection.AutoFill _
Destination:= _
NewTempl.Range("A23:BT" & (23 + Prod_count - 1)), _
Type:=xlFillDefault

I don't believe that changing just the range in the above code makes a
differences in the code wroking and not working

NewTempl.Range("A23:BT27"), _


"Mikaela" wrote:

If you're referring to this part of the code,

ThisWorkbook.Sheets("footer"). _
Range("A1").Copy _
Destination:= _
NewTempl1.Range("A" & (23 + Prod_count))

I changed the code to be like that because the "footer" value needs to
appear on the *next* line after the last product row. Example: 23 + 4 product
rows is A27. The product rows will occupy up to A26 while the "footer" value
will be on A27.

In the code that I used (23 + 5 - 1 = 27), 5 is a constant value chosen at
random to temporarily substitute your autofill code that wasn't working. In
my master list the number of products is arbitrary. Your code is the one I
need because it counts the number of products per Product Family ID. I don't
think there's a no difference between your Prod_count & my Prod_count.....
I've checked by putting in a msgbox to prompt the value of "A23:BT" & (23 +
Prod_count - 1). Example: 23 + 4 product rows - 1 = "A23:BT26".

If the cause of the error can't be determined & fixed, is it possible to do
some kind of workaround ? At worst, I can fill column AB in the Masterlist
sheet with the number of product rows for each Product Family ID and let the
code read the Prod_count from there (Similar to how the code reads the
Recipients from column AA).

I tried the protection code but am receiving this error "Compile Error.
Expected: Named parameter".

TIA


"Joel" wrote:

There is one place in the code where "23 + Prod_count " needs to be changed to
"23 + Prod_count - 1").

When it fails check the value of Prod_count. Your code uses 5 (23 + 5 - 1 =
27). The problem could be that my code is calculating a different value for
Prod_count.


The protection problem should be solved by unprotecting all features when
the code is run. Then at the end of the code protect only some of the
features.

from
NewTempl1.Protect "12345678", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
to
NewTempl1.Protect "12345678", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
EnableOutlining = True



"Mikaela" wrote:

Thanks for your quick reply. I used F8 to step thru the code and found that
this part caused the error is :

Selection.AutoFill _
Destination:= _
NewTempl.Range("A23:BT" & (23 + Prod_count - 1)), _
Type:=xlFillDefault

It throws out this error msgbox "Run-time error '1004'. Application-defined
or object-defined error". I added NewTempl & Product_count to the watch
window. In the watch window, the moment it reaches that part of code the
values in these variables turn to "<Out of context".

When an explicitly defined range like "A23:BT27" is used, the autofill
works... most of the time. I'm not sure why it wouldn't work all the time
(I'm making a wild guess that Excel is confused with the selection since more
than one workbook is being handled :P)

As you can't reproduce the error, I wonder whether I did something on my
side. When you post your code I tweak it a teeny bit to suit me 100% (the
code that works for me is below verbatim). Also, a few days ago I changed
the name of the template sheet in the "Template.xlt" file from "Template" to
"PRODUCT TEMPLATE" and I also changed the code to cater to this. I wonder if
this modification is preventing the autofill from working.....

One last request... I need to change the protection properties to enable
outlining to work in the protected template sheet. I.e. something like this:

NewTempl.Protect Password:="12345678", userinterfaceonly:=True
NewTempl.EnableOutlining = True

If I use the above code, it throws an error at the autofill part of the code
(I was using explicitly defined range for the autofill while I was testing
this).

Appreciate your help.

TIA

=============================

Sub Macro9()
'
' Macro9 Macro
'
Set fs = CreateObject("Scripting.FileSystemObject")
'
With ThisWorkbook.Sheets("MasterList")
LastRow = .Cells(Rows.Count, "Z").End(xlUp).Row
'Start Row is 1st row of a Product ID
startrow = 2
For RowCount = 2 To LastRow
If .Cells(RowCount, "Z") < _
.Cells(RowCount + 1, "Z") Then

Prod_ID = .Cells(RowCount, "Z")
Prod_count = RowCount - startrow + 1
Workbooks.Add _
Template:="C:\MasterList\template.xlt"
Set NewBook = ActiveWorkbook
Set NewTempl = NewBook.Sheets("PRODUCT TEMPLATE")
Set NewTempl1 = NewBook.Sheets("PRODUCT TEMPLATE")
NewTempl.Activate

NewTempl.Unprotect ("12345678")

NewTempl.Range("A23:BT23").Select

'Commenting out because this part throws an error
'Selection.AutoFill _
Destination:= _
NewTempl.Range("A23:BT" & (23 + Prod_count - 1)), _
Type:=xlFillDefault

'In order to test entire macro, using explicit-defined range for autofill
Selection.AutoFill _
Destination:= _
NewTempl.Range("A23:BT27"), Type:=xlFillDefault

.Range("A" & startrow & ":O" & RowCount).Copy
NewTempl.Range("A23").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

' check for "Asia Pacific"
For RCount = 23 To (23 + Prod_count - 1)
If NewTempl.Range("C" & RCount) = "Asia Pacific" Then
NewTempl.Range("E" & RCount).Locked = False
NewTempl.Range("E" & RCount).Interior.ColorIndex = 6
End If
Next RCount

.Range("R" & startrow & ":Z" & RowCount).Copy
NewTempl1.Range("BL23").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
NewTempl1.Columns("BJ:BT"). _
EntireColumn.Hidden = True

ThisWorkbook.Sheets("footer"). _
Range("A1").Copy _
Destination:= _
NewTempl1.Range("A" & (23 + Prod_count))

.Range("Z" & RowCount).Copy
NewTempl1.Range("E3").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

With ThisWorkbook.Sheets("amt tracking")
'Find Prod_ID
Set c = .Columns("A:A").Find(what:=Prod_ID, LookIn:=xlValues)
.Range("B" & c.Row).Copy
NewTempl1.Range("B9").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Range("C" & c.Row).Copy
NewTempl1.Range("B10").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Range("D" & c.Row).Copy
NewTempl1.Range("B11").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With

NewTempl1.Protect "12345678", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

recipient = .Range("AA" & RowCount)
Path = "C:\MasterList\"
Set folder = _
fs.GetFolder(Path)
Set mysubfolder = folder.subfolders
found = False
For Each file In mysubfolder
If file.Name = recipient Then
found = True
Exit For
End If