| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Simplifing Code
The code below works, but I'm sure it can be simplified. Any help would be
appreciated. Am using this project to help me learn VBA. Sub Populate_Charts() 'This Populates the Chart with the Week Ending Dates from the Pivot Table Worksheets("Pivot").Activate Range("B4").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C25").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Rows("25:25").Select Selection.NumberFormat = "d-mmm-yy" 'This Populates the Chart with Average Planned Manpower Worksheets("Pivot").Activate Range("B10").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C26").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.NumberFormat = "#,##0.0" Worksheets("Pivot").Activate Range("B5:B5").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C28:C28").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.NumberFormat = "0.0%" Worksheets("Pivot").Activate Range("B6:B6").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C29:C29").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.NumberFormat = "0.0%" ActiveCell.CurrentRegion.Select Selection.Borders(xlEdgeLeft).Weight = xlThin Selection.Borders(xlEdgeRight).Weight = xlThin Selection.Borders(xlEdgeTop).Weight = xlThin Selection.Borders(xlEdgeBottom).Weight = xlThin Selection.Borders(xlInsideHorizontal).Weight = xlThin Selection.Borders(xlInsideVertical).Weight = xlThin End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help Simplifing Code
Don, Thanks.
After changing the current range for the borders this works great. The code is much more efficient and runs faster than the original code. I had looked at someone's code using active cell and wasn't sure how to change the code to select from a designated cell. I also didn't know how to set the borders around a range of cells. Thanks to you I do now. "Don Guillett" wrote in message ... Something like this should be a bit better. Not sure where you wanted borders. Sub copyem()'Works from ANYWHERE in worbook With Worksheets("Pivot") x = .Cells(4, 2).End(xlToRight).Column .Range(.Cells(4, 2), .Cells(4, x)).Copy Sheets("charts").Range("c25").PasteSpecial Paste:=xlValues .Range(.Cells(10, 2), .Cells(10, x)).Copy Sheets("charts").Range("c26").PasteSpecial Paste:=xlValues .Range(.Cells(5, 2), .Cells(5, x)).Copy Sheets("charts").Range("c28").PasteSpecial Paste:=xlValues .Range(.Cells(6, 2), .Cells(6, x)).Copy Sheets("charts").Range("c29").PasteSpecial Paste:=xlValues End With With Sheets("charts") .Rows("25:25").NumberFormat = "d-mmm-yy" .Rows("26:26").NumberFormat = "#,##0.0" .Rows("28:29").NumberFormat = "0.0%" .Range("b6").CurrentRegion.Borders.Weight = xlThin End With End Sub -- Don Guillett SalesAid Software "Joel Mills" wrote in message ... The code below works, but I'm sure it can be simplified. Any help would be appreciated. Am using this project to help me learn VBA. Sub Populate_Charts() 'This Populates the Chart with the Week Ending Dates from the Pivot Table Worksheets("Pivot").Activate Range("B4").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C25").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Rows("25:25").Select Selection.NumberFormat = "d-mmm-yy" 'This Populates the Chart with Average Planned Manpower Worksheets("Pivot").Activate Range("B10").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C26").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.NumberFormat = "#,##0.0" Worksheets("Pivot").Activate Range("B5:B5").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C28:C28").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.NumberFormat = "0.0%" Worksheets("Pivot").Activate Range("B6:B6").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C29:C29").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.NumberFormat = "0.0%" ActiveCell.CurrentRegion.Select Selection.Borders(xlEdgeLeft).Weight = xlThin Selection.Borders(xlEdgeRight).Weight = xlThin Selection.Borders(xlEdgeTop).Weight = xlThin Selection.Borders(xlEdgeBottom).Weight = xlThin Selection.Borders(xlInsideHorizontal).Weight = xlThin Selection.Borders(xlInsideVertical).Weight = xlThin End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help Simplifing Code
glad to help.
-- Don Guillett SalesAid Software "Joel Mills" wrote in message ... Don, Thanks. After changing the current range for the borders this works great. The code is much more efficient and runs faster than the original code. I had looked at someone's code using active cell and wasn't sure how to change the code to select from a designated cell. I also didn't know how to set the borders around a range of cells. Thanks to you I do now. "Don Guillett" wrote in message ... Something like this should be a bit better. Not sure where you wanted borders. Sub copyem()'Works from ANYWHERE in worbook With Worksheets("Pivot") x = .Cells(4, 2).End(xlToRight).Column .Range(.Cells(4, 2), .Cells(4, x)).Copy Sheets("charts").Range("c25").PasteSpecial Paste:=xlValues .Range(.Cells(10, 2), .Cells(10, x)).Copy Sheets("charts").Range("c26").PasteSpecial Paste:=xlValues .Range(.Cells(5, 2), .Cells(5, x)).Copy Sheets("charts").Range("c28").PasteSpecial Paste:=xlValues .Range(.Cells(6, 2), .Cells(6, x)).Copy Sheets("charts").Range("c29").PasteSpecial Paste:=xlValues End With With Sheets("charts") .Rows("25:25").NumberFormat = "d-mmm-yy" .Rows("26:26").NumberFormat = "#,##0.0" .Rows("28:29").NumberFormat = "0.0%" .Range("b6").CurrentRegion.Borders.Weight = xlThin End With End Sub -- Don Guillett SalesAid Software "Joel Mills" wrote in message ... The code below works, but I'm sure it can be simplified. Any help would be appreciated. Am using this project to help me learn VBA. Sub Populate_Charts() 'This Populates the Chart with the Week Ending Dates from the Pivot Table Worksheets("Pivot").Activate Range("B4").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C25").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Rows("25:25").Select Selection.NumberFormat = "d-mmm-yy" 'This Populates the Chart with Average Planned Manpower Worksheets("Pivot").Activate Range("B10").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C26").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.NumberFormat = "#,##0.0" Worksheets("Pivot").Activate Range("B5:B5").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C28:C28").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.NumberFormat = "0.0%" Worksheets("Pivot").Activate Range("B6:B6").Activate Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets("Charts").Activate Range("C29:C29").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.NumberFormat = "0.0%" ActiveCell.CurrentRegion.Select Selection.Borders(xlEdgeLeft).Weight = xlThin Selection.Borders(xlEdgeRight).Weight = xlThin Selection.Borders(xlEdgeTop).Weight = xlThin Selection.Borders(xlEdgeBottom).Weight = xlThin Selection.Borders(xlInsideHorizontal).Weight = xlThin Selection.Borders(xlInsideVertical).Weight = xlThin End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
| Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
| Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
| option buttons run Click code when value is changed via VBA code | Excel Programming | |||
| VBA code delete code but ask for password and unlock VBA protection | Excel Programming | |||
Linear Mode
