Tweak required to run in Excel 2000

Macro runs fine in Excel 2007 but not Excel 2000

Page 1 of 1

0 Replies - 720 Views - Last Post: 23 January 2009 - 03:12 AM Rate Topic: -----

#1 pboltonchina  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 31-July 08

Tweak required to run in Excel 2000

Posted 23 January 2009 - 03:12 AM

Hi Everyone,

I have a macro that works perfect on Excel 2007 (my home PC) but not on Excel 2000 (my work PC). Apparently Excel 2000 does not use 'SearchFormat', so it stops and gives an error. The problem is I don't know how to alter the code so it will run on either PC.

Thanks for looking, any help greatly appreciated.

Here's my code

Sub Generate_Repair_Kit_List()
'
'
 '   Clear or Add a Results sheet
			If SheetExists("List") Then
			  Sheets("List").Activate
			  Range("A1").Select
			  Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
			  Selection.ClearContents
			Else
				Sheets.Add After:=Sheets(Sheets.Count)
				ActiveSheet.Name = "List"
			End If
  On Error Resume Next
  For Each ws In Sheets
	ws.Activate
	If ActiveSheet.Name <> "List" Then
' execute code
End If
Dim c As Range
With Application.FindFormat.Interior
.ColorIndex = 15
End With
Set c = Cells.Find(What:="", SearchFormat:=True)
If c Is Nothing Then
Else
c.Activate
	Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
	Selection.Copy
	Sheets("List").Activate
	Range("C65366").End(xlUp).Offset(2, -2).Select
	ActiveSheet.Paste
Columns("A:F").Select
Selection.Columns.AutoFit
 End If
 Next ws
 Set CurSheet = ActiveSheet
 Sheets.Select
 Range("A1").Select
 Selection.ClearContents
 CurSheet.Activate
End Sub
Private Function SheetExists(SheetName As String) As Boolean
	' Returns TRUE if a sheet exists in the active workbook
	Dim x As Worksheet
		On Error Resume Next
			Set x = ActiveWorkbook.Sheets(SheetName)
				If Err = 0 Then SheetExists = True _
				Else SheetExists = False
End Function






Thanks
Paul

Is This A Good Question/Topic? 0
  • +

Page 1 of 1