On 3 jan, 03:53, ljgent wrote:
I developed a technical pack template to be used for each style that is
designed.
Designer opens tech pack and on the first sheet she fills in the heading
with information such as
style #
description
fabric
fabric content
factory
these are all in different cells
When she is done she saves the tech pack using the style # so the tech pack
template can be used for another style.

What I would like to do but do not know how to is in another workbook called
techbook log is when the designer fills in the information that it gets
logged into the techbook log.

The techbook log will have column headings

style # * Description * Fabric * Fabric Content * Factory

Is there a way to have the log book automatically be filled in for each tech
pack that is created.
If there are 20 styles there would be 20 tech packs therefore there would be
20 entries into the techbook log. *Can this be done and if so how?

Greatly appreciate your help.

Hi Ijgent,

In Excel 2003 I have created the macro below.
It should be placed in the workbook macro set of the technical pach
template.
Change the line with Set wrkLog = ... to the correct location

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wrbLog As Workbook
Dim lngRow As Long
Dim blnLogged As Boolean

Set wrbLog = Workbooks.Open("C:\...\techbook_log.xls")

If IsEmpty(wrbLog.Sheets("Sheet1").Cells(2, 1)) Then
lngRow = 2
Else
lngRow = 1
Do
lngRow = lngRow + 1
Loop Until wrbLog.Sheets("Sheet1").Cells(lngRow, 1).Value _
ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value Or _
IsEmpty(wrbLog.Sheets("Sheet1").Cells(lngRow, 1))

If Not IsEmpty(wrbLog.Sheets("Sheet1").Cells(lngRow, 1)) Then
If wrbLog.Sheets("Sheet1").Cells(lngRow - 1, 1).Value = _
ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value Then
' style already logged : modify
lngRow = lngRow - 1
Else
' create empty row
wrbLog.Sheets("Sheet1").Cells(lngRow,
1).EntireRow.Select
Selection.Insert Shift:=xlDown
End If
End If

End If
' copy info
wrbLog.Sheets("Sheet1").Cells(lngRow, 1).Value = _
ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value
wrbLog.Sheets("Sheet1").Cells(lngRow, 2).Value = _
ThisWorkbook.Sheets("Sheet1").Cells(2, 2).Value
wrbLog.Sheets("Sheet1").Cells(lngRow, 3).Value = _
ThisWorkbook.Sheets("Sheet1").Cells(3, 2).Value
wrbLog.Sheets("Sheet1").Cells(lngRow, 4).Value = _
ThisWorkbook.Sheets("Sheet1").Cells(4, 2).Value
wrbLog.Sheets("Sheet1").Cells(lngRow, 5).Value = _
ThisWorkbook.Sheets("Sheet1").Cells(5, 2).Value

ThisWorkbook.Activate
wrbLog.Save
wrbLog.Close

End Sub


HTH,

Wouter