Hi Guys
I have a macro on a workbook, and i am getting n numbers of new workbook on daily basis, i am totally new about the macros,need help to load that macro on all sheets in with the help of another macro, if possible, how can i edit that macro so that can pull data from folder execute that on sheet and save it with the same name. here is the macro which i have , your help will be appreciated.
Thanks
Mahesh
Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Y
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R1048576C5", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:="Data!R5C10", TableName:="PivotTable7", DefaultVersion _
:=xlPivotTableVersion12
Sheets("Data").Select
Cells(5, 10).Select
ActiveSheet.PivotTables("PivotTable7").DisplayFieldCaptions = False
ActiveSheet.PivotTables("PivotTable7").Name = "PT"
With ActiveSheet.PivotTables("PT")
.ColumnGrand = False
.EnableDrilldown = False
.RowGrand = False
.SaveData = False
.DisplayContextTooltips = False
.ShowDrillIndicators = False
.SortUsingCustomLists = False
End With
With ActiveSheet.PivotTables("PT").PivotFields("ACTION")
.PivotItems(" ").Visible = False
.PivotItems("ACTION").Visible = False
.PivotItems("Add").Visible = False
.PivotItems("Change Mylar").Visible = False
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PT").PivotFields("ACTION")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PT").PivotFields("UPC")
.Orientation = xlRowField
.Position = 2
End With
Range("K5").Select
ActiveWorkbook.Worksheets("Data").PivotTables("PT").PivotCache. _
CreatePivotTable TableDestination:="Data!R5C11", TableName:="PivotTable8", _
DefaultVersion:=xlPivotTableVersion12
Sheets("Data").Select
Cells(5, 11).Select
ActiveSheet.PivotTables("PivotTable8").Name = "PA"
With ActiveSheet.PivotTables("PA")
.ColumnGrand = False
.EnableDrilldown = False
.RowGrand = False
.DisplayFieldCaptions = False
.DisplayContextTooltips = False
.ShowDrillIndicators = False
.SortUsingCustomLists = False
End With
With ActiveSheet.PivotTables("PA").PivotFields("ACTION")
.PivotItems(" ").Visible = False
.PivotItems("ACTION").Visible = False
.PivotItems("Change Mylar").Visible = False
.PivotItems("Delete").Visible = False
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PA").PivotFields("ACTION")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PA").PivotFields("UPC")
.Orientation = xlRowField
.Position = 2
End With
Range("L5").Select
ActiveWorkbook.Worksheets("Data").PivotTables("PA").PivotCache. _
CreatePivotTable TableDestination:="Data!R5C12", TableName:="PivotTable9", _
DefaultVersion:=xlPivotTableVersion12
Sheets("Data").Select
Cells(5, 12).Select
ActiveSheet.PivotTables("PivotTable9").Name = "PM"
With ActiveSheet.PivotTables("PM")
.ColumnGrand = False
.EnableDrilldown = False
.RowGrand = False
.DisplayFieldCaptions = False
.DisplayContextTooltips = False
.ShowDrillIndicators = False
.SortUsingCustomLists = False
End With
With ActiveSheet.PivotTables("PM").PivotFields("ACTION")
.PivotItems(" ").Visible = False
.PivotItems("ACTION").Visible = False
.PivotItems("Add").Visible = False
.PivotItems("Delete").Visible = False
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PM").PivotFields("ACTION")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PM").PivotFields("UPC")
.Orientation = xlRowField
.Position = 2
End With
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Down:=-6
Range("J5:L60").Select
ActiveWindow.SmallScroll Down:=-18
Columns("J:L").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("L15").Select
End Sub
Bookmarks