Sharing info across 2 workbooks

[S Willingham]

I am using Excel 2003 and I have a lot to learn.

I have two workbooks. One is a QUOTE WORKBOOK and on is a JOB RECAP. The
QUOTE WORKBOOK is used to quote and process jobs. It contains a series of
worksheets. Once the information about the specific job is entered it is
saved as the “job nameâ€. The JOB RECAP is a running total of the jobs sold.

I would like to automate the process. I would like to add a cell on the
QUOTE WORKBOOK so that when I enter “Active†it will “send†info to the JOB
RECAP sheet like “Job nameâ€, “Invoice amountâ€, etc.

Is this possible? If so How?

Thanks in Advance.

Steve

 

[Joel]

The steps you have specified require a custom macro. The best way for a
beginer to accomplish this task is through a learn macro. Then post the
learrn macro on this site to get help in modifying it to be a general macro
that can be used over and over again. Usually learned macros will work for
only one of your jobs but not for all jobs.

Follow these steps.
1) Create a new Quote workbook and save the file under job name so nothing
gets lost.
2) Make sure Job recap workbook is closed.
3) Start a learn Macro. To do this go to the Tools Menu and select Macros
and then
select Record new Macro.
4) Add all the job name information to the Job recap workbook.
5) stop Recording Macro. Again go to the Tools Menu and seclect Macro -
stop Recording.
6) Now copy the macro. On the Tab at bottom of worksheet with worksheet
name right click and select view code. In the VBAProjectt window under
module there will be a module with code. The 1st line of the code (which is
a subroutine or macro) will start with SUB and the last line of the code will
had END SUB. Copy all the code and paste it into a Posting at this wqebsite.
Ask for it to be modified to be modified to be a general macro.

The general macro should contain a Message box asking for the Job name. The
job name should be part of the XLS filename so the file can be found.


If you have any questions or need additional info I will be glad to assist.
Just reply to this posting.

 

[S Willingham]

Joel, Thanks for the response.

when you say "4) Add all the job name information to the Job recap workbook."

is this simply done by using = (in the cell to be populated) followed by the
cell that contains the information or is there a better way for this
application.

Thanks

 

[Joel]

For 4) I meant use your normal procedure of copy and paste or add links.
What ever steps you normally do to add the qutoe workbook data into the Job
recap workbook.

 

[S Willingham]

here is the Macro I recorded

Sub JobRecap()
'
' JobRecap Macro
' Macro recorded 3/11/2007 by FaroTemplate
'

'
Range("J5").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Application.WindowState = xlMinimized
Range("A2").Select
ActiveSheet.Paste
ActiveWindow.WindowState = xlNormal
Windows("macro test.xls").Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
Windows("JOB RECAP.xls").Activate
Windows("macro test.xls").Activate
Range("J7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("JOB RECAP.xls").Activate
Range("B2").Select
ActiveSheet.Paste
Windows("macro test.xls").Activate
ActiveWindow.SmallScroll Down:=9
Range("B43").Select
Application.CutCopyMode = False
Selection.Copy
Windows("JOB RECAP.xls").Activate
ActiveSheet.Paste
Range("C2").Select
Windows("macro test.xls").Activate
Range("B41").Select
Application.CutCopyMode = False
Selection.Copy
Windows("JOB RECAP.xls").Activate
ActiveSheet.Paste
Range("D2").Select
Windows("macro test.xls").Activate
ActiveWindow.SmallScroll Down:=18
Range("B59").Select
Application.CutCopyMode = False
Selection.Copy
Windows("JOB RECAP.xls").Activate
ActiveSheet.Paste
Range("E2").Select
Windows("macro test.xls").Activate
Range("B39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("JOB RECAP.xls").Activate
ActiveSheet.Paste
Columns("A:A").ColumnWidth = 20.86
Columns("B:B").ColumnWidth = 22.14
Columns("C:C").ColumnWidth = 24.29
Columns("D").ColumnWidth = 24.86
Columns("E:E").ColumnWidth = 34.86
Windows("macro test.xls").Activate
End Sub

I need it to be modified to be a general macro. It should contain a message
box asking for the job name.

Thanks for the help.

Steve

 

[Joel]

I modified the code to make it more general.

1) It will add the data to the end of the Job Recap workbook. It searches
column A for the 1st empty cell.

2) didn't know the worksheet name for Job Recap so I made it Sheet1. You
can change this.

3) These are the cell I think you are copying. I made of made a mistake.
It can easily be fixed. do you want to copy, or should they be links? I can
change this.

a) from:Job Recap J5 to:Job Recap A2
b) from: Quoto B43 to:Job Recap B2
c) from: Quoto B41 to:Job Recap C2
d) from: Quoto B59 to:Job Recap D2
e) from: Quoto B39 to:Job Recap E2

Sub JobRecap()
'
' JobRecap Macro
' Macro recorded 3/11/2007 by FaroTemplate
'
Const RECAPWorksheetName = "Sheet1"


LastRow = Workbooks("JOB RECAP.xls").Worksheets(RECAPWorksheetName). _
Range("A2:A1000").End(xlDown).Row

If LastRow = 65536 Then
If IsEmpty(Workbooks("JOB RECAP.xls").Worksheets(RECAPWorksheetName). _
Range("A2").Value) Then

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


JobName = InputBox("Enter Job Name")

'
Workbooks("JOB RECAP.xls").Worksheets(RECAPWorksheetName). _
Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks("JOB RECAP.xls").Worksheets(RECAPWorksheetName). _
Range("J5")

' this looks like an error, so I commented it out
' Workbooks("JOB RECAP.xls").Worksheets(RECAPWorksheetName). _
' Range("B2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
' Workbooks("Quote Workbook.xls").Worksheets(JobName).Range("J7")

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

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

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

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


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

 

[S Willingham]

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]

It is either the name of the worksheet of the name of the workbook don't
match. Workbook is the .xls filename without the path name. EXCEL doesn't
like the path name in the workbook(" ") statement.

when you get the error highlight : RECAPWorksheetName
Then right click and add to watch.

Do the same think for: JobName
make sure these names match the ones in your workbook and the tabs on your
worksheet.

Also make sure both the JOB RECAP.xls and Quote Workbook.xls workbooks are
opened. I did not open these workbooks. I assume they would already be
opened.

 

[Joel]

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]

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]

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(RECAPWorksheetName). _
Range("A2:A1000").End(xlDown).Row

If LastRow = 65536 Then
If
IsEmpty(Workbooks(RECAPWorkbookName).Worksheets(RECAPWorksheetName). _
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(RECAPWorksheetName). _
Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks(RECAPWorkbookName).Worksheets(RECAPWorksheetName). _
Range("J5")

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

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

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

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

Workbooks(RECAPWorkbookName).Worksheets(RECAPWorksheetName). _
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").ColumnWidth = 24.86
Workbooks(RECAPWorkbookName).Worksheets("Sheet1"). _
Columns("E:E").ColumnWidth = 34.86
End Sub

 

[S Willingham]

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!

 

[S Willingham]

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]

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]

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]

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]

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]

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(RECAPWorksheetName). _
Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks(RECAPWorkbookName).Worksheets(RECAPWorksheetName). _
Range("J5")

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

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

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

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

Workbooks(RECAPWorkbookName).Worksheets(RECAPWorksheetName). _
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.

 

[S Willingham]

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

Steve

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(RECAPWorksheetName). _
Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _
Workbooks(RECAPWorkbookName).Worksheets(RECAPWorksheetName). _
Range("J5")

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

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

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

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

Workbooks(RECAPWorkbookName).Worksheets(RECAPWorksheetName). _
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.


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



:

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