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

Home / Uncategorized / 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


Answer:

Read more

Leave a Reply

Your email address will not be published. Required fields are marked *