| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sharing info across 2 workbooks
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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" wrote: 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" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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" wrote: 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" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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: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" wrote: 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" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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 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: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" wrote: 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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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 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: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" wrote: 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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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. "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 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: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" wrote: 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" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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 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: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" wrote: 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" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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 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: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" wrote: 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" wrote: 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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 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: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" wrote: 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" wrote: 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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 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: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" wrote: 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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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 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: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" wrote: 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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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 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: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. |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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 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 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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. 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 |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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. 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") |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sharing info across 2 workbooks
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sharing workbooks | Excel Discussion (Misc queries) | |||
| Sharing Info between two workbooks? | Excel Worksheet Functions | |||
| Sharing workbooks | Excel Discussion (Misc queries) | |||
| Sharing Workbook Info message ? | Excel Discussion (Misc queries) | |||
| Sharing Information Between Workbooks | Excel Worksheet Functions | |||
Linear Mode
