Forum Discussion
Macro only run once
I did 2 macros which one is for creating Pivot table and another to clear the file but they will only run once. I think it is because of the sheet number. However I am not sure how to fix this problem please help guide me through
Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A6"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), _
Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array( _
38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), _
Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array( _
51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), _
Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array( _
64, 1), Array(65, 1), Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1)), _
TrailingMinusNumbers:=True
Range("A6").Select
Application.CutCopyMode = False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R6C1:R37C69", Version:=6).CreatePivotTable TableDestination:= _
"Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ACTUAL MATURITY DATE")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TOTAL NET AMOUNT")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MERCHANT NAME")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("TOTAL NET AMOUNT"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("TOTAL NET AMOUNT"). _
LayoutForm = xlTabular
ActiveSheet.PivotTables("PivotTable1").PivotFields("ACTUAL MATURITY DATE"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("ACTUAL MATURITY DATE"). _
LayoutForm = xlTabular
End Sub
and
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+z
'
Range("A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A6").Select
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
End Sub
2 Replies
joh532485 OK I don't want to parse out all that your macro does because it is rather messy and i could probably spend a lot time to clean it up but may not even matter to you. that said here is what I think you need to fix the 1st macro. the 2nd macro that deletes the sheet and most of the data you could do something but I fear it is risky because it could easily delete the wrong sheet. Why not just lock the file and make it read only so they open it, make changes and have to Save As, or not, and then when they close it and re-open it as a clean workbook.
Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+q
'dim wsNew as Worksheet
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
...Range("A6").Select
Application.CutCopyMode = False
set wsNew = Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R6C1:R37C69", Version:=6).CreatePivotTable TableDestination:= _
wsNew.name &"!R3C1", TableName:="PivotTable1", DefaultVersion:=6
wsNew.Select
