Range to be copied changes

C

ConnieM_brd

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
 
D

Don Guillett

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
(e-mail address removed)
 
C

ConnieM_brd

I am so happy you responded...but I am too dumb to understand. Please help me

Don Guillett said:
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
(e-mail address removed)
ConnieM_brd said:
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
Click to expand...
Click to expand...
 
D

Don Guillett

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
(e-mail address removed)
ConnieM_brd said:
I am so happy you responded...but I am too dumb to understand. Please help
me

Don Guillett said:
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
(e-mail address removed)
ConnieM_brd said:
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
Click to expand...
Click to expand...
Click to expand...
 
C

ConnieM_brd

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()
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 said:
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
(e-mail address removed)
ConnieM_brd said:
I am so happy you responded...but I am too dumb to understand. Please help
me

Don Guillett said:
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
(e-mail address removed)
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
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

Try it, as posted, not within another sub. If all else fails, send your
workbook to my address below along with a complete clear explanation and
before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
ConnieM_brd said:
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()
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 said:
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
(e-mail address removed)
ConnieM_brd said:
I am so happy you responded...but I am too dumb to understand. Please
help
me

:

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
(e-mail address removed)
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Don - Please help on another subject - headers 1
Error Message - Macro to Unlink MS Queries from Workbook 0
Macro for Creating Tables in Excel 2007 8
Different Results from the Same Macro 2
Macro Related to Paste Values 2
Run macro from active workbook whose name keeps changing 20
How to hide password? 1
10 seconds to open excel workbook w/macros 1

Top