Help with this Macro

[S Willingham]

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

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

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


'QuoteWorkbook = InputBox("Enter Job Name")


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

MsgBox "The JobRecap has been updated!"

'End If
End Sub

 

[Don Guillett]

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

 

[S Willingham]

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]

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.