Thread: Help Simplifing Code
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Don Guillett[_4_] Don Guillett[_4_] is offline
external usenet poster
  
Posts: 2,337
Default 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 With QuoteReply With Quote