|
Help with this Macro
Hi Guys,
Wondered if somebody could give a look at the following code and tell me how to add a level of functionality to it. Basically it "sends" info from certain cells in one workbook (QuoteWorkbook) to another workbook (JobRecap) I would like to have the macro "check" for duplicates. If cells A2, B2 and C2 in the JobRecap workbook are duplicated it would return a msg and the macro would stop. Thanks in advance Sub JobRecap() ' ' JobRecap Macro ' Macro recorded 3/11/2007 by FaroTemplate ' Const RECAPWorkbookName = "JOB RECAP.xls" Const RECAPWorksheetName = "Job Recap" Const JobName = "Info sheet" QuoteWorkbook = ActiveWorkbook.Name 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 'QuoteWorkbook = InputBox("Enter Job Name") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("B6") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B6") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("B2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("E8") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("C2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("E36") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("D2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("E10") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("E2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("E12") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("F2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B22") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("G2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("K20") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("H2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("M34") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("I2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("M14") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("J2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("P38") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("K2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B34") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("L2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B36") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("M2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B26") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("N2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B30") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("P2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B28") Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("A:A").ColumnWidth = 30 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("B:B").ColumnWidth = 23 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("C:C").ColumnWidth = 23 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("D:D").ColumnWidth = 12 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("E:E").ColumnWidth = 26 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("F:F").ColumnWidth = 12 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("G:G").ColumnWidth = 12 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("H:H").ColumnWidth = 14 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("I:I").ColumnWidth = 15.5 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("J:J").ColumnWidth = 15.5 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("K:K").ColumnWidth = 22 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("L:L").ColumnWidth = 22 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("M:M").ColumnWidth = 12.7 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("N:N").ColumnWidth = 15 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("O:O").ColumnWidth = 13.7 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("P:P").ColumnWidth = 26 MsgBox "The JobRecap has been updated!" 'End If End Sub |
Help with this Macro
|
Help with this Macro
I guess it depends of what your definition of "fine" is.
IMO to do what you desire your code would have to use FIND or FINDNEXT saying if found don't copy... But, as you say, "The macro works fine as is". Glad you are happy. -- Don Guillett SalesAid Software "S Willingham" wrote in message ... Don, Thanks for the input. The macro works fine as is. I am really wanting to see how to make it work a little better. Any input on how to make it check for duplicates? Thanks Steve "Don Guillett" wrote: Looks like a lot of changes could be in order 1. Learn to use the WITH statement with workbooks("myworkbook.xls").sheets("mysheet") ' ...... use ONE dot before ie: .cellls lastrow=.cells(rows.count,"a").end(xlup).row ' or +1 for the next row 'Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = '.Range("A2").Offset(lastrow,0) = 'or even this line instead ..cells(lastrow,"a") end with 2. combine your columnwidth requests example: range("a1, p1,z1").entirecolumn.columnwidth=26 columns("b:d").columnwidth=22 ETc -- Don Guillett SalesAid Software "S Willingham" wrote in message ... Hi Guys, Wondered if somebody could give a look at the following code and tell me how to add a level of functionality to it. Basically it "sends" info from certain cells in one workbook (QuoteWorkbook) to another workbook (JobRecap) I would like to have the macro "check" for duplicates. If cells A2, B2 and C2 in the JobRecap workbook are duplicated it would return a msg and the macro would stop. Thanks in advance Sub JobRecap() ' ' JobRecap Macro ' Macro recorded 3/11/2007 by FaroTemplate ' Const RECAPWorkbookName = "JOB RECAP.xls" Const RECAPWorksheetName = "Job Recap" Const JobName = "Info sheet" QuoteWorkbook = ActiveWorkbook.Name 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 'QuoteWorkbook = InputBox("Enter Job Name") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("B6") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("A2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B6") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("B2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("E8") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("C2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("E36") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("D2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("E10") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("E2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("E12") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("F2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B22") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("G2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("K20") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("H2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("M34") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("I2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("M14") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("J2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("P38") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("K2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B34") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("L2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B36") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("M2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B26") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("N2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B30") Workbooks(RECAPWorkbookName).Worksheets(RECAPWorks heetName). _ Range("P2").Offset(rowoffset:=Myrowoffset, columnoffset:=0) = _ Workbooks(QuoteWorkbook).Worksheets(JobName).Range ("B28") Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("A:A").ColumnWidth = 30 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("B:B").ColumnWidth = 23 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("C:C").ColumnWidth = 23 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("D:D").ColumnWidth = 12 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("E:E").ColumnWidth = 26 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("F:F").ColumnWidth = 12 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("G:G").ColumnWidth = 12 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("H:H").ColumnWidth = 14 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("I:I").ColumnWidth = 15.5 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("J:J").ColumnWidth = 15.5 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("K:K").ColumnWidth = 22 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("L:L").ColumnWidth = 22 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("M:M").ColumnWidth = 12.7 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("N:N").ColumnWidth = 15 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("O:O").ColumnWidth = 13.7 Workbooks(RECAPWorkbookName).Worksheets("Job Recap"). _ Columns("P:P").ColumnWidth = 26 MsgBox "The JobRecap has been updated!" 'End If End Sub |
| All times are GMT +1. The time now is 12:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com