0

I would like to make my macro work in any sheet and not sure how. The below macro is used on Sheet 5 and Sheet 6 but I'd like it to work in any sheet for example sheet 1 or 2.

There must be a way to do it but I wouldn't know how to do it.

Can anyone help please?

Thanks,

Ceiran

Sheets.Add After:=ActiveSheet
Sheets("2a. Fixed - Cost Forecast").Select
Cells.Select
Selection.Copy
Sheets("Sheet5").Select
ActiveSheet.Paste
Rows("4:4").Select
Range("D4").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Columns("E:AG").Select
Selection.Delete Shift:=xlToLeft
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Cells.Select
Selection.AutoFilter
Selection.AutoFilter
ActiveSheet.Range("$A$1:$BT$157").AutoFilter Field:=4, Criteria1:="="
Rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.AutoFilter
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("AB1").Select
ActiveCell.FormulaR1C1 = "Total"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Partner"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1:AB1048575").Select
Range(Selection, Selection.End(xlUp)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet5!R1C1:R129C28", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="Sheet6!R3C1", TableName:="PivotTable8", DefaultVersion _
    :=xlPivotTableVersion15
Sheets("Sheet6").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Partner")
    .Orientation = xlRowField
    .Position = 1
End With

End Sub

ceiran1990
  • 1
  • First, I recommend that you pay attention to [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Алексей Р Jun 21 '21 at 09:02
  • It is difficult to be helped using so many selection and do not clearly stating which sheets are involved. You are talking about Sheet5 and Sheet6, but nothing about `ActiveSheet` and the new sheet added of the beginning. Are they different than the mentioned ones? If not, which is which? Should `Sheets("2a. Fixed - Cost Forecast")` to be used independent of the used sheets you are referring to? – FaneDuru Jun 21 '21 at 09:09

0 Answers0