Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to be copied changes
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to be copied changes
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to be copied changes
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to be copied changes
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 "ConnieM_brd" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
V look up and data that goes out when copied in range | Excel Discussion (Misc queries) | |||
Copied range. Help please!! | Excel Programming | |||
copied cell range | Excel Programming | |||
Determining last copied range | Excel Programming | |||
Adress of Copied Range | Excel Programming |