Macro To compare column A on two worksheets

[fishingsantafe]

Here is a Macro I recorded.

Sub insertcolumn() 
' 
' insertcolumn Macro 
' Macro recorded 07/20/2007 by e337ljs 
' 
' Keyboard Shortcut: Ctrl+t 
' 
Columns("B:B").Select 
Selection.Insert Shift:=xlToRight 
Range("B1").Select 
ActiveCell.FormulaR1C1 = "New files" 
With ActiveCell.Characters(Start:=1, Length:=9).Font 
.Name = "Tahoma" 
.FontStyle = "Bold" 
.Size = 8 
.Strikethrough = False 
.Superscript = False 
.Subscript = False 
.OutlineFont = False 
.Shadow = False 
.Underline = xlUnderlineStyleNone 
.ColorIndex = 55 
End With 
Range("B2").Select 
ActiveCell.FormulaR1C1 = _ 
"=ISNA(MATCH(RC[-1],'July 07'!RC[-1]:R[43]C[-1],FALSE))" 
Range("B2").Select 
End Sub

The July 07 in the formula is the name of the worksheet before the active sheet. Is there a way to tell it to go to the previous worksheet Look at column A which will vary as to how many rows to see if the information in A2 of the active worksheet is found in column A of the previous sheet. If it is not found in column A then enter "true" in B of the active work sheet. If it is found on in column A on the previous sheet then enter "false" in B2 on the active worksheet. ( I want to compare numbers in Column A on active to Column A on the Worksheet before the active one to find new infomation that was added to the current months worksheet.

This works but I have always change the name of the worksheet every month I run it and I also have to change the range of the previous worksheet because this will vary every month. Is there and easy way to tell it to stop looking when it hits a blank row in column A on the previous worksheet? Any help is appreciated. I am a beginner at VB but am learning more each day. Hope this is not to confusing. I use Excel 2003

 

[xenou]

Sub insertcolumn()
Dim mySheetIndex As Integer
Dim myLastMonthSheetName As String
Dim myFormula as String
Dim lRow as Long

'Insert new column
Columns("B:B").Insert Shift:=xlToRight

'Column Header
Range("B1").FormulaR1C1 = "New files"
With Range("B1").Font
    .Name = "Tahoma"
    .FontStyle = "Bold"
    .Size = 8
    .ColorIndex = 55
End With

'Make sure we are not on the first sheet in the workbook
'If not, get sheet name of last month's sheet
If ActiveSheet.Index = 1 Then
    MsgBox "An error occurred. This sub will not run on sheet 1."
    End
Else
myLastMonthSheetName = Worksheets(ActiveSheet.Index - 1).Name
End If

'Determine last row in last month's data
lRow = Worksheets(myLastMonthSheetName).Cells(Rows.Count, 1).End(xlUp).Row

'Build a string to use as a formula
myFormula = "=ISNA(MATCH(RC[-1],'" & _
    myLastMonthSheetName & _
    "'!RC[-1]:R[" & lRow & "]C[-1],FALSE))"

'The formula is put in Cell B2
Range("B2").FormulaR1C1 = myFormula

'End on Cell B2
Range("B2").Select

End Sub

I suppose we could have worked around getting the last row of last month's data by just using 1,000 rows or 10,000 rows for the lookup range...but this routine will check for the last row with data in the previous sheet in Column A and use that row number.

Some quick notes:
I cleaned up some of the defaults in the font properties (usually you can just erase some of the default values Excel records, especially with fonts, borders, and page setups).

I also removed the selection that Excel records:
Range("B2").Select
Selection.Delete

Can be:
Range("B2").Delete

And I took advantage of the index property to do what you needed here -- you can refer to a sheet by name or by index number. And of course if a sheet is Sheet(5), the sheet before it (last month) will be Sheet(4).

Regards.

 

[fishingsantafe]

Thanks for all your time. I will give it a try. I will need to study it all to see how you did it. So I can use part in the future. I am trying to learn all this.

 

[fishingsantafe]

I entered it in to excel and when I run the compiler to make sure everything is ok I get a Compile Error "invalid outside procedure" and it highlights :=xlToRight in the first line of code when I am trying to insert the column.

I even tried to put the original code that was recorded for inserting the column but then it gives me the same error for Columns("B:B").Select-- the ("B:B")part of the code. Any help is appreciated.

 

[fishingsantafe]

Never mind I figured it out on my own and the code now works. Thanks for your time it is appreciated.