Excel - macro example

[wwj]

Excel macro
- get a user input for row number
- copy a sheet : Data and plot/chart
- rename the sheet
- Filter data from the original sheet (with redrawing)
- Change the chart's title.

Code: Select all

Sub NewRowDraw01()
'
' NewRowDraw01 Macro
'
'
    Dim rowno As Integer
    Dim sheetname As String
    
    rowno = InputBox("Row number?")
    Sheets("R-Std").Select
    Sheets("R-Std").Copy Before:=Sheets(1)
    Sheets("R-Std (2)").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = rowno
    sheetname = "R-" & Range("A2")
    Sheets("R-Std (2)").Name = sheetname
    Range("B1").Select
    Sheets("AllTopAltitude").Select
    ActiveSheet.Range("$A$1:$K$24427").AutoFilter Field:=1
    ActiveSheet.Range("$A$1:$K$24427").AutoFilter Field:=1, Criteria1:=rowno
    Range("A1:K24427").Select
    Selection.Copy
    Sheets(sheetname).Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "Geological Units at Row=" & rowno
    ActiveChart.ChartArea.Select
    Range("A1").Select
End Sub