Thread: How to copy rows into an Excel *template* with vba
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Mikaela Mikaela is offline
external usenet poster
  
Posts: 15
Default How to copy rows into an Excel *template* with vba
I tested the code. It worked quite well & is VERY close to what I wanted to
do, except for a few parts (listed below). Am not sure I know why a template
& template1 worksheet is needed (I'm new at this, pls bear with me).

1. The autofill of A23:BT23 in the template worksheet in the new Template
workbook created from Template.xlt doesn't work for some reason. The format
doesn't autofill down to the number of product rows needed.
2. When copying values from "amt tracking" sheet in Master.xls into B9, B10
& B11 in template worksheet, the values that need to be copied are in columns
B, C & D *depending* on Product Family ID in column A. Example, if Product
Family ID is "XYZ" located in A13, then B13, C13 & D13 in "amt tracking"
sheet is copied into B9, B10 & B11 in the template worksheet.

Sorry if my explanation wasn't clear enough in the past.

Also, how do I modify the code so that:
1. In the template worksheet, after data is pasted from Master.xls (like in
the code snippet below), if value of the cell in column D equals "Asia
Pacific", then the corresponding cell in column E's unlocked & hidden
property must be false, and the cell background changed color to yellow.

..Range("A" & startrow & ":O" & RowCount).Copy
NewTempl.Range("A23").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.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


2. After the new workbook is saved, the workbook will be closed.

TIA

NewTempl.Unprotect ("12345678")
.Activate
.Range("A23:BT23").Select
Selection.AutoFill _
Destination:= _
Range("A23:BT" & (23 + startrow - 1)), _
Type:=xlFillDefault
.Range("A" & startrow & ":O" & RowCount).Copy
NewTempl.Range("A23").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Range("R" & startrow & ":Z" & RowCount).Copy


"Joel" wrote:

Try this code. Will not guarentee it will work the first try. there were
differences between your description and the macro and wasn't sure which was
correct. Macro contains both a templete and templete1 worksheet. The code
below use both templetes even though your description only had one.


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("Template")
Set NewTempl1 = NewBook.Sheets("Template1")
NewTempl.Select

NewTempl.Unprotect ("12345678")
.Activate
.Range("A23:BT23").Select
Selection.AutoFill _
Destination:= _
Range("A23:BT" & (23 + startrow - 1)), _
Type:=xlFillDefault
.Range("A" & startrow & ":O" & RowCount).Copy
NewTempl.Range("A23").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.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")
.Range("B2").Copy
NewTempl1.Range("B9").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Range("C2").Copy
NewTempl1.Range("B10").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Range("D2").Copy
NewTempl1.Range("B11").PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With

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

NewTempl.Unprotect ("12345678")

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
Next file
If found = False Then
mysubfolder.Add (recipient)
End If

NewBook.SaveAs Filename:= _
Path & recipient & "\template_" & _
Prod_ID & "_" & _
recipient & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

startrow = RowCount + 1
End If
Next RowCount
End With
End Sub


"Mikaela" wrote:


Hi Joel,

Thanks for your reply.

The samples I posted before were simplified examples so I could explain what
I needed without confusing people too much.... As I'm recording the macro,
I'm posting the actual thing he

Master list of products - Master.xls
Excel template - Template.xlt

Step-by-step of the manual process:
==========================

1. Open "Master.xls".

2. Create new workbook from "template.xlt". Choose disable macros when
opening.

3. In the new workbook, go to "Template" sheet:
- Unprotect this sheet (Password is "12345678").
- Select AT23:BT23 & drag down till row 28. (AT23:BT23 is basically the data
row with cells containing special formats, formulas, UDFs, etc. How many rows
that needs to be created with the drag down depends on how many products
there are with same ProductFamilyID (Column Z in "MasterList" sheet in
Master.xls). In this example it is 6 rows)

4. Go to "MasterList" sheet in "Master.xls", select & copy A2:O7. In the new
workbook, go to "Template" sheet & 'Paste Special - values' into A23:O28.

5. Go to "MasterList" sheet in "Master.xls", select & copy R2:Z7. In the new
workbook, go to "Template" sheet & 'Paste Special - values' into BL23:BT28.
Then HIDE columns BJ to BT.

5. Go to "footer" sheet in "Master.xls" & select & copy A1. In the new
workbook, sheet "Template", paste into the first cell of the next row after
the last data row. In this example, paste into A29.

6. In new workbook, sheet "Template", 'Paste Special - values' the
ProductFamilyID into Cell E1. ProductFamilyID. ProductFamilyID is in Z column
of "MasterList" sheet in "Master.xls".

7. Go to "amt tracking" sheet in "Master.xls". Column A is the
ProductFamilyID and Columns B to D is are number values associated with it.
For that ProductFamilyID (Column A) that is being worked on, I need to paste
the corresponding values (Column B to D) into the new workbook "Template"
sheet:
- On "amt tracking" sheet in "Master.xls", select & copy B2. Go to new
workbook, sheet "Template", and 'Paste Special - values' into B9.
- On "amt tracking" sheet in "Master.xls". select & copy C2. Go to new
workbook, sheet "Template", and 'Paste Special - values' into B10.
-. On "amt tracking" sheet in "Master.xls", select & copy D2. Go to new
workbook, sheet "Template", and 'Paste Special - values' into B11.

8. There's some open groupings (i.e. plus & minus signs) in the columns in
the template. Close the groupings in column Y, AG, AR, AX, BA, BE.

9. Protect the "Template" sheet (Password is "12345678").

10. Create new folder for the workbook recipient (A recipient can be linked
to more than one ProductFamilyID. The example here is "alanhudson"). Save
workbook as "template_(ProductFamilyID)_(RecipientName).xl s" (example
ProductFamilyID is ZA1112C3, recipient is "AlanHudson").

ProductFamilyID is located in "MasterList" sheet in "Master.xls" Z column
(Z2 onwards), RecipientName in AA column (AA2 onwards).

11. Close the saved workbook.

Recorded macro code:
================
Code for Macro recording:
Sub Macro9()
'
' Macro9 Macro
'

'
Workbooks.Add Template:="C:\MasterList\template.xlt"
Cells.Select
ActiveSheet.Unprotect
Range("A23:BT23").Select
ActiveWindow.SmallScroll Down:=9
Selection.AutoFill Destination:=Range("A23:BT28"), Type:=xlFillDefault
Range("A23:BT28").Select
ActiveWindow.LargeScroll ToRight:=-3
Windows("Master.xls").Activate
Range("A2:O7").Select
Selection.Copy
Windows("template1").Activate
Range("A23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Master.xls").Activate
Range("R2:Z7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("template1").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 59
Range("BL23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.LargeScroll ToRight:=-3
ActiveWindow.SmallScroll Down:=3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 11
Reply With QuoteReply With Quote