Excell Programming

Creating New Sheet




We can create sheets by Sheets.Add and since this returns a
Worksheet object, you can either name it directly:

Sheets.Add.Name = "Test"

or use an object and name it later

Set Sh = Sheets.Add

Sh.Name = "Test"




Getting Sheet  from name.

pName = ActiveWorkbook.Path      ' the path of the
currently active file

wbName = ActiveWorkbook.Name     ' the file name of the
currently active file

shtName = ActiveSheet.Name       ' the name of the
currently selected worksheet







The first sheet in a workbook can be referenced by

ActiveWorkbook.Worksheets(1)




For deleting the [Report] tab you would use

ActiveWorkbook.Worksheets("Report").Delete




Getting name of indexed Sheet

shtName = ActiveWorkbook.Worksheets(1).Name




to "work on that sheet later on" you can create a range
object like

Dim MySheet as Range

MySheet = ActiveWorkbook.Worksheets(shtName).[A1]

and continue working on MySheet(rowNum, colNum) etc. ...




shortcut creation of a range object without defining shtName:

Dim MySheet as Range

MySheet = ActiveWorkbook.Worksheets(1).[A1]




Concatenate String

shtName = ActiveWorkbook.Worksheets(1).Name

Sheets.Add.Name = shtName & "mahendra"




Setting font to selected Cells

Select Sheet

Sheets("shtName").Select

Select the Cells:

ActiveCell.Select

OR

Rows(1).Select

OR

Columns(2).Select




Set the Font:

Selection.Font.Bold = True




Setting value to cell

Cells(1, 1).Value = "Name"







Getting
color code for vba

http://dmcritchie.mvps.org/excel/colors.htm







showing
on message dialog

MsgBox Count




Getting
last row of excel

LastRow = Cells.Find("*", searchorder:=xlByRows,
searchdirection:=xlPrevious).Row




Getting
Active worksheet object in excel

currentShtName = Application.ActiveSheet.Name




Changing
column width 

Columns("B:E").ColumnWidth = 25




To autofit
the column

Columns(2).AutoFit




To align a whole column

Columns("A:W").HorizontalAlignment = xlCenter