| 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 |
| 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 | |||
Hybrid Mode
