RE: Additional info needed

Hi Muiz. Thanks for the query and welcome to the forum. First of all here’s the SaveMe subroutine:
-------------------------------------------------------------------------------------------

Sub saveme()

Dim mynewfilename As String

mynewfilename = "C:\Documents and Settings\User10\Desktop\" & strName & ".xls"

Sheets(strName).Select
Sheets(strName).Copy
ActiveWorkbook.SaveAs Filename:=mynewfilename

ActiveWorkbook.Close

End Sub
-------------------------------------------------------------------------------------------

…here is code illustrating various Worksheet Events:

-------------------------------------------------------------------------------------------
Option Explicit

Private Sub Worksheet_Activate()
MsgBox "The Activate event occurs when the user enters the sheet"
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "The BeforeRightClick event runs code prior to showing the context menu"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "The Change event occurs whenever a cell is altered and Enter pressed"
End Sub

Private Sub Worksheet_Deactivate()
MsgBox "The Deactivate event occurs when the user switches to another sheet"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "The SelectionChange event occurs every time the user selects a range"
End Sub

-------------------------------------------------------------------------------------------

…and finally here is the code for a very basic chart:

-------------------------------------------------------------------------------------------
Option Explicit

'First off, you can't call this module "Charts" - that's a reserved keyword

Sub BarClustered()

Dim aChart As Chart

'This is an object variable
'It allows us to reference the props and methods of that object

'Load the variable with a new chart:

Set aChart = Charts.Add

'If we commented out everything from this point onwards
'...it will create a standalone chart sheet

'Now pick up the area report sheet:

Sheets("Area Report").Select

'Peculiarity alert! The organisation of the Chart object is ... eccentric
'For example, we have to use the set command a SECOND time here...

Set aChart = aChart.Location(where:=xlLocationAsObject, Name:="Area Report")

'If you put a break point in here. The result is a blank chart object straight onto Area Report
'So now build the chart:

With aChart

.ChartType = xlBarClustered

.SetSourceData Source:=Sheets("Area Report").Range("A1").CurrentRegion, PlotBy:=xlColumns

'This method has two arguments. "PlotBy" is the equivalent of using the chart wizard to plot by rows or columns

.HasTitle = True 'If you leave this out it will default to false and will fall over in next line, saying there's no title

.ChartTitle.Text = "Sales and Commission"

'Now set the column colors

.SeriesCollection(1).Interior.Color = vbRed
.SeriesCollection(2).Interior.Color = vbBlue

'Turn the plot area transparent:

.PlotArea.Interior.ColorIndex = xlNone

'put the values on the end of the columns

.ApplyDataLabels xlDataLabelsShowValue

'specify where the chart is going to go:
'Every chart is actually a child object of a worksheet sheet
'therefore as a child object, it has a parent object, pointing back at the sheet!

With .Parent

'we are going down the object model here
'specify the top and left position of the chart
.Top = Range("f7").Top
.Left = Range("f7").Left


End With

End With

'The measurements here are in millimetres.
'...but screen resolution affects the final display.
'Use trial and error for sizing here...

ActiveSheet.Shapes(1).Width = 500
ActiveSheet.Shapes(1).Height = 250

'make the corners of the chart rounded!

ActiveSheet.DrawingObjects(1).RoundedCorners = True

'So...why now a drawingobject and not a chart
'The object model for charts gets messy here

'Use the macro recorder as far as you can.
'In 2003 you can only drill so far into a chart while recording a macro
'In 2007 and beyond the macro recorder records every chart change
'So record yourself doing the formatting, then clean up the code.

Range("a1").Select

End Sub

-------------------------------------------------------------------------------------------

Hope this helps,

Anthony