thanks alot Joel. I'll play with those formulas. I really appreciate all
your help.

Steve

"Joel" wrote:

to copy the formula a statement like this would be used

Range("J5").Formula = "='[My workbook.xls]Sheet1'!$J$7"

for your code we would replace this

from
Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("J5")

to
------------------------------------------------------------------------------------------------
Myformula = "J5"

Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0).formula
= _
Myformula

---------------------------------------------------------------------------------------------
for the other copy statements
from
Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("B2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B43")

to
Myformula = "'[" + QuoteWorkbook + "]" + JobName + "'!" + "B43"

Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("B2").Offset(rowoffset:=Myrowoffset, columnoffset:=0).formula
= _
Myformula

Note thbere are single and double quotes together "'[" is a double quote
followed by a single quote. Also at "'!"

--------------------------------------------------------

to change Quote workbook name. i was expectting you to ask this question so
I planned to make it simple.

replace this statement
Const QuoteWorkbook = "Quote workbook"

with
QuoteWorkbook = Inputbox("Enter Job Name")


You may also need this
QuoteWorkbook = QuoteWorkbook + ".xls"

Sometime Excel need the .xls and somettimes it doesn't. Excel is funny that
it doesn't always need everything.


----------------------------------------------------------



"CM" wrote:


"S Willingham" wrote:

Good points Joel.

What I have is a Workbook that is basically an empty shell. It contains
ablout 20 worksheets that are used to quote, process and bill the countetops
I sell. The first worksheet is the "Info sheet" and it contains the
pertinant info about the customer, the job and the details of the job. Once
the fields are entered in the "info sheet" the data is linked to the various
other sheets within the workbook.

I use the workbook 3 ways.
I add information to the "customer" portion of the sheet like Company, Name
address etc and save the workbook as "(customer name) quote template"
I then create quotes as needed using the customers template. As I quote
jobs I save the workbook as the job name.
If the Quote becomes an Active Job I complete the info sheet with the job
information and I am able to automate the paperwork.

I'm not sure if this is the "best" way to accomplish this but it seems to
work.

I agree that linking the info is better than copying. Is there an easy way
for me to re-write what I currently have to link the data rather than copy it?

Also, How would I go about adding to the macro I have so that it will allow
me to either send the info from a workbook of a certain name (the Job Name)
Right now it works great but it only sends the info from "Quote Workbook".
Perhaps it could be set to use the text bos that was part of your original
macro.

And I really like the idea of having a check system so that jobs are not
entered more than once.

Thanks again for the help.

Steve


"Joel" wrote:

I would think that transfering the data and renaming the file are both one
time events and can be part of the same macro. A check can be made in the
macro not to change the name if the worksheet name is "Info sheet"

The advantage to link is if a correction is made. The link will get updated
automatically. With copying, you have to remember to make the change in both
workbooks.

Right now the additions in the Job Recap workbook is automatically done on
the last row. No check is made of the job name to see if it is already
included in the Job Recap sheet. There is a posibility that a job can end up
twice in the workbook. I don't know if you want that to happen or don't want
it to happen!

A macro can be writen that would perform an update which would find the job
in the Recap workbook and then change the present line rather tthan add to
the end of the list. Actually, good programming practices would modify the
present macro to check the Job Recap workbook for the job name before adding
new information.

I only no the informattion that has been given to me and don't really know
all your needs and requirments. I think you are new tto programming and
haven't clearly thought out all your requirements.

You have created a small macro that will help save you time and increase the
accuracy of your work. Writing software has a hiden enhancement that most
people don't consider. It eliminate stupid mistakes which will save money!

"S Willingham" wrote:

I would like a to be able to save the workbook as the job name and copy or
link to the Job Recap workbook. Is there an advantage one way or the other,
linking vs copying? I really need these to be seperate macros because I dont
need to transfer the info for each and every quote. Once they become actual
jobs I then would perform the macro.

Thanks

"Joel" wrote:

Additions can be added to the macro that would do the following

1) Add Pop up box asking for job name
2) rename worksheet to match job name
3) save file as job name.
4) We copied the data to the Job Recap workbook. We could make these links
instead.



"S Willingham" wrote:

BINGO!

everything works great now. All the required info transferes to JOB RECAP.
Can It be set so that I can save the "Quote Workbook" as the job name and
still have the info transfer. For instance it I quote the Smith job and save
it as "Smith.xls"

Thanks for all the help!


"Joel" wrote:

I'm trying the best I can with the infomation you are providing. The Job Rec
Workbook and workshhet lok ok because the 1st copy statement which copies
from one cell in this workbook to another cell is working. the problem is in
the Quote workbook. I removed the Pop up window.

We can add additional code later (after this works) to save the file under a
new name or what ever else that would make it easy to do your job.

If you have any more problems change these 4 statements as necessary in the
code below.. these line define the two workbook names and the two worksheets
you are using. They ae the only thing that can be wrong.

DON'T CHANGE HERE, CHANGE BELOW DOTTED LINE
1) Const RECAPWorkbookName = "JOB RECAP.xls"
2) Const RECAPWorksheetName = "Sheet1"
3) Const QuoteWorkbook = "Quote Workbook.xls"
4) Const JobName = "Info sheet"


CODE STARTS BELOW DOTTED LINE!!!
--------------------------------------------------------------------------------------------

Sub JobRecap()
'
' JobRecap Macro
' Macro recorded 3/11/2007 by FaroTemplate
'
Const RECAPWorkbookName = "JOB RECAP.xls"
Const RECAPWorksheetName = "Sheet1"
Const JobName = "Info sheet"
Const QuoteWorkbook = "Quote workbook"

LastRow = Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("A2:A1000").End(xlDown).Row

If LastRow = 65536 Then
If
IsEmpty(Workbooks(RECAPWorkbookName).Worksheets(RE CAPWorksheetName). _
Range("A2").Value) Then

Myrowoffset = 0
Else
Myrowoffset = 1
End If
Else
Myrowoffset = LastRow - 1
End If


' JobName = InputBox("Enter Job Name")

'
Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("J5")

' this looks like an error, so I commented it out
' Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
' Range("B2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
' Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B7")

Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("B2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B43")

Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("C2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B41")

Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("D2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B59")

Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _
Range("E2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B39")


Workbooks(RECAPWorkbookName).Worksheets("Sheet1"). _
Columns("A:A").ColumnWidth = 20.86
Workbooks(RECAPWorkbookName).Worksheets("Sheet1"). _
Columns("B:B").ColumnWidth = 22.14
Workbooks(RECAPWorkbookName).Worksheets("Sheet1"). _
Columns("C:C").ColumnWidth = 24.29
Workbooks(RECAPWorkbookName).Worksheets("Sheet1"). _
Columns("D:D").ColumnWidth = 24.86
Workbooks(RECAPWorkbookName).Worksheets("Sheet1"). _
Columns("E:E").ColumnWidth = 34.86
End Sub





"S Willingham" wrote:

The "Quote Workbook" is renamed for the job quoted once the info is entered.
Ex "Smith kitchen" the worksheet that contains the info is called "Info
Sheet"

does that help.

Thanks

"Joel" wrote:

Looking again. It can be only 2 problems.

1) The Quote workbook isn't opened or isn't named Quote Workbook
2) The name you typed in the Pop Up window didn't match the worksheet name
in the Quote workbook

"S Willingham" wrote:

Joel, Thanks for all your help thus far.

I really appreciate all you have done.

When I run the macro I am getting a run-time error '9'

When I debug it highlights this

Workbooks("JOB RECAP.xls").Worksheets(RECAPWorksheetName). _
Range("B2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks("Quote Workbook.xls").Worksheets(JobName).Range("B43")

any ideas?

"Joel" wrote:

I modified the code to make it more general.