Help Simplifing Code

[Joel Mills]

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

 

[Don Guillett]

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

 

[Joel Mills]

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]

glad to help.