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.

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



"S Willingham" wrote:

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