How to set active sheet as variable in below recorded macro for excel 2013?

Question

I recorded a macro in excel. I have do this with excel to generate report every week. Position and number of columns remains same. But, Number of rows change every week. Same way file name and sheet name changes.

This recorded macro is recorded for sheet named "2017_08_13". I have to change the sheet name manually in this macro every time. Same way cell selection Goes to row number 101. Everytime I have to manually enter the last row number to get it working. Can anyone help me to make this two things work for every sheet automatically. 1) It will run on activesheet so, instead of sheet name, we can make it to go with active sheet. 2) It should go upto last cell to select. In line 17 ( I guess) it is pointing to go up to row 101.

Sub Macro1()
'
' Macro1 Macro
'

'
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
ActiveCell.FormulaR1C1 = "Decimal"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*24"
Range("E2").Select
Selection.Copy
Range("D2").Select
Selection.End(xlDown).Select
Range("E101").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-105
Application.CutCopyMode = False
Selection.NumberFormat = "General"
ActiveWindow.SmallScroll Down:=-126
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
Range("A1:F1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Columns("F:F").EntireColumn.AutoFit
Range("H2").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "2017_08_13!R1C1:R101C6", Version:=xlPivotTableVersion15). _
    CreatePivotTable TableDestination:="2017_08_13!R2C8", _
    TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion15
Sheets("2017_08_13").Select
Cells(2, 8).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("activity")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Decimal"), "Sum of Decimal", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Down:=-105
End Sub

I am not a programming guy. Thank you

I **highly** recommend reading through [how to avoid `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/). At the very least, you'd want to do `Dim ws as Worksheet // Set ws = Sheets("mySheetName") // ws.Range("A1")Value = ...` to make sure you're accessing "mySheetName"'s A1 cell. — BruceWayne, Aug 15 '17 at 05:18

score 0 · Answer 1 · answered Aug 15 '17 at 13:44

If you want to loop through the sheets, without using names, you can use:

Dim i as Integer, j as Integer, LR as Long

j = Sheets.Count

For i = 1 to j

    With Sheets(i)

        LR=.Cells(.Rows.Count, "A").End(xlUp).Row 'Allows you to have a dynamic row count

        'Your code here, where you should see .Columns("E:E") rather than Columns("E:E") to make use of the With statement

    End With

Next i

How to set active sheet as variable in below recorded macro for excel 2013?

1 Answers1