Help with Extending code (Comparing Two Sheets)

[BigH]

Hi there,

the code below does everything i need it too do, what it does is insert a
formula, the formula compares information on 2 sheets. Basically the 1st
formula compares what was on last week and not on this week, the second
formula does vise versa and the 3rd formula shows what parts were common to
both sheets.
What i do manually each week is copy and paste these formulas all the
way down to row 1000. then select range b2:h1000, data sort then delete any
blank spaces (remembering that these blanks actually have formulas in them),
i do the same with j21000 and i do the same with q2:x1000.
I then copy and paste specialvalues all of the information from b2:h???
range (this varies every week as does each of the other ranges)
on to the end of range q2:x??????, i then copy all the information from
j2???? onto the end of q2:x????.

If someone could show me how to automate the above I would be very
thankfull.

Sub Insert_Formula()
Application.ScreenUpdating = False
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("1103 Working Sheet Last Week").Select
Cells.Select
Selection.ClearContents
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("1103 NonAdhD Last Week").Select
Cells.Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("1103 Working Sheet Last Week").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Rows("1:36").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Columns("A").Select
Selection.Delete Shift:=xlToLeft
Columns("B:H").Select
Selection.Delete Shift:=xlToLeft
Range("B1").Select
Application.Run "PERSONAL.XLS!SumAll"
Range("B1").Select
ActiveCell.FormulaR1C1 = "'Value"
Range("C2").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Compare Working Sheet").Select
Range("A1").Select
With ActiveSheet
.Range("B3").Formula _
= "=IF(COUNTIF('1103 Working Sheet Last Week'!A:A," _
& "'1103 Working Sheet'!A2)=0,'1103 Working
Sheet'!A2,"""")"
.Range("H3").Formula _
= "=VLOOKUP(B3,'1103 Working Sheet'!$A$2:$B$1500,2,FALSE)"
.Range("J3").Formula _
= "=IF(COUNTIF('1103 Working Sheet'!A:A," _
& "'1103 Working Sheet Last Week'!A2)=0,'1103 Working Sheet
Last Week'!A2,"""")"
.Range("O3").Formula _
= "=VLOOKUP(J3,'1103 Working Sheet Last
Week'!$A$2:$B$1500,2,FALSE)"
.Range("Q3").Formula _
= "=IF(COUNTIF('1103 Working Sheet'!A:A," _
& "'1103 Working Sheet Last Week'!A2)=1,'1103 Working Sheet
Last Week'!A2,"""")"
.Range("R3").Formula _
= "=IF(ISNA(VLOOKUP(Q3,'1103 NonAdhD This
Week'!$E$37:$F$1500,2,FALSE))," _
& "VLOOKUP(Q3,'1103 NonAdhD Last Week'!$E$37:$F$1500,2,FALSE),"
_
& "(VLOOKUP(Q3,'1103 NonAdhD Last
Week'!$E$37:$F$1500,2,FALSE)))"
.Range("S3").Formula _
= "=IF(ISNA(INDEX('1103 NonAdhD Last
Week'!$C$37:$E$1000,MATCH(Q3,'1103 NonAdhD Last Week'!$E$37:$E$1000,0),1)),"
_
& """"",(INDEX('1103 NonAdhD This
Week'!$C$37:$E$1000,MATCH(Q3,'1103 NonAdhD This Week'!$E$37:$E$1000,0),1)))"
.Range("U3").Formula _
= "=IF(ISNA(VLOOKUP(Q3,'1103 NonAdhD This
Week'!$E$37:$G$1000,3,FALSE)),"""",(VLOOKUP(Q3,'1103 NonAdhD Last
Week'!$E$37:$G$1000,3,FALSE)))"
.Range("V3").Formula _
= "=VLOOKUP(Q3,'1103 Working Sheet Last
Week'!$A$1:$B$1000,2,FALSE)"
.Range("W3").Formula _
= "=VLOOKUP(Q3,'1103 Working Sheet'!$A$2:$B$1500,2,FALSE)"
.Range("X3").Formula _
= "=W3-V3"
Application.ScreenUpdating = True

End With
End Sub



thanks BigH