[steve420]

I have a template form we use for creating job quotes. We've been using the updatelogworksheet macro below to add within the workbook itself, but the other macros that I had to create to reset the main form after every use and general lack of knowledge by other users made this not useful in that earlier logs would be overwritten or the form would not reset properly. Is there a way to create a log of history in a separate workbook to track this separately and maintain the blank template form? macro follows. Using Excel 2003 in XP. Steve Sub UpdateLogWorksheet() Dim historyWks As Worksheet Dim inputWks As Worksheet Dim nextRow As Long Dim oCol As Long Dim myRng As Range Dim myCopy As String Dim myCell As Range 'cells to copy from Input sheet - some contain formulas myCopy = "D12,D17,G14,G16,J48,J51" Set inputWks = Worksheets("ONLY") Set historyWks = Worksheets("sheet1") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With With historyWks With .Cells(nextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With .Cells(nextRow, "B").Value = Application.UserName oCol = 3 For Each myCell In myRng.Cells historyWks.Cells(nextRow, oCol).Value = myCell.Value oCol = oCol + 1 Next myCell End With End Sub '

[Keebellah]

Better send a workbook with dummy data and we'll take a look.

The log sheet should of course be available to all using the template sheet.

[steve420]

Here is a sample of how the form is currently. The problem is with a template, the Quotes sheet stays the same every time the template is opened. The form is on a shared network drive, and my intent is to put a tracking list workbook on the same shared drive, so the template form is always the same for users and the tracking form is to check open quotes at a glance. I have tried adding open workbook commands to the macro, but I can't figure out how to get the macro to look at the other workbook. The macro limits itself to the current quotes workbook template or the tracking workbook.

[Keebellah]

I think I understand you.
What you should do in your vba module

Public wba as workbook
Pubic wbb as workbook

this way you don't have to Dim them every time.

Application.Workbooks.Open (<workbookname>), ReadOnly:=True

where workbookname is the fullpath and filename
Since you call it from your workbook you can access all te macros and do whatever you need just keeping in mind that you reference the correct workbook(s)

wba.sheets("quotes")range("A2").value = wbb.sheets(whatever.Range("B3").value
etc etc.

I hope you get the idea.

[steve420]

Somewhat. Your last note about the wba.sheets range. Does that go in the string area of the macro.

Also "wba" and "wbb" are replaced by the actual file names or am I doing that incorrectly?

[Keebellah]

in the function, to reference the workbooks:

set wba = activeworkbook.name
set wbb = the name of the workbook you are about to open.xls or xlst or the extension it's saved with in offie 2007

the all you have to do is refer to it

wba.sheet("A").range("B3").value = wbb.sheet( "X").Range("f6").value

just try things out.

You can also record a macro once the other sheet is opened and then see how it's coded.
That's how I started with macro's

Bedtime for me now, its'00:16

I'll read your post tomorrow.
I have an example somewhere of something similar and I'll send it

[steve420]

I keep getting a type mismatch compile error trying to "set" the destination workbook or an object required runtime error 424. I have the two set as public now, and no trouble setting the active workbook. I have tried opening the destination workbook before set, or placing it after "set" does not seem to make any difference.

Set wbb = quotetracking.xls.Name

I have tried including the folders (as used in the open file command), with and w/o the extension, with and w/o the ".name"

[Keebellah]

You got yoiur syntax wrong
An example:

Dim wkbData As Workbook
Set wkbData = Workbooks.Open(filename:=ActiveWorkbook.Path & "\KPLTS\Kostenplaats.xls", ReadOnly:=True)