ExcelBanter - Range to be copied changes

I have this now in for the macro:

Sub Last_Title_Web()
'
' Last_Title_Web Macro
' Last name with Title and formated for the web
'

'
Sub ltw()
With Sheets("LAST_TITLE")
.UsedRange.Delete
Sheets("all").UsedRange.Copy .Range("a1")
.Columns("e").Delete
lr = .Cells.Find("*", Cells(Rows.Count, 1), , , xlByRows, xlPrevious).Row
..Range("a1:g" & lr).Name = "Table1"
With Range("Table1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(row(),2)=0"
.FormatConditions(1).Interior.ColorIndex = 15
End With
End With
End Sub

And I receive an error complie error. I am sorry I am so 'slow' on this. I
know how to record a macro, but reading and editing, I get lost.

"Don Guillett" wrote:

Did you test using this instead of what you were using? I didn't test in
2007 but it should work.
save your file as a different name and test to see it does as desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"ConnieM_brd" wrote in message
...
I am so happy you responded...but I am too dumb to understand. Please help
me

"Don Guillett" wrote:

try this
Sub ltw()
With Sheets("LAST_TITLE")
.UsedRange.Delete
Sheets("all").UsedRange.Copy .Range("a1")
.Columns("e").Delete
lr = .Cells.Find("*", Cells(Rows.Count, 1), , , xlByRows, xlPrevious).Row
..Range("a1:g" & lr).Name = "Table1"
With Range("Table1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(row(),2)=0"
.FormatConditions(1).Interior.ColorIndex = 15
End With
End With
End Sub

Don Guillett
Microsoft MVP Excel
SalesAid Software

"ConnieM_brd" wrote in message
...
I am working with a phone directory listing. On the ALL sheet is where
I
list all listings. I will be adding and deleting listings as time goes
on.
I then want to copy from ALL into another worksheet (Last_Title) where
I
paste, then I will delete a column and then I need to format the
listings
to
have gray background every other row. But the range changes. Example I
will
add a new listing to the ALL worksheet, and it copies just find to
Last_Title, but the formating doesn't continue for all the listings,
example
if I add a listing to the top of ALL, the Last_Title row will not have
the
correct grey or not grey formating. I created this macro, is there a
way
that I can PAUSE the macro for user input to select the range or a way
that I
can program to find the first blank row and to FORMAT the gray
backgound?

Sub Last_Title_Web()
'
' Last_Title_Web Macro
' Last name with Title and formated for the web
'

'
Sheets("LAST_TITLE").Select
Cells.Select
Selection.Delete Shift:=xlUp
Sheets("ALL").Select
Cells.Select
Selection.Copy
Sheets("LAST_TITLE").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$320"), ,
xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight1"
Range("Table1[[#Headers],[Last]]").Select
Selection.AutoFilter
End Sub