VBA copy paste runs different in debug mode

Question

I have a spreadsheet in Excel with three pivot charts. I wrote a VBA code that does the following:

' Copies sheets 1-4
ThisWorkbook.Worksheets(Array("1", "2", "3", "4")).Copy

'Transforms each new sheet data in values only.
For Each iSheet In ActiveWorkbook.Sheets
    iSheet.Activate
    iSheet.UsedRange.Copy
    iSheet.UsedRange.PasteSpecial xlPasteValuesAndNumberFormats
    iSheet.Range("A1").Select
Next iSheet

' After pasting, deletes the contents of the cells but keeps the graphs
' in spread sheet 4
ActiveWorkbook.Sheets("4").Columns("J:AB").Clear

This code runs differently in debug mode (F8) compared to when it is run by pressing F5 or running with a button in Excel. In debug mode, it keeps all the series information in the Pivot Charts when the Pivot Tables are pasted as values. When it runs nonstop, it loses the pivot charts information, so that for instance the series names change to defaults 'Series 1', the x values go from specific date values to jan-00, format of x axis labels change to text, etc. I have no idea why. In other very similar question, someone pointed out that PasteSpecial behaves differently when the target worksheet is not activated, which is why I added iSheet.Activate. This difference in action may also be a result of the Clear method of the Pivot Tables.

I tried to add Activate in different steps in the code to no avail.

FYI, as a general rule, you want to [avoid the use of Activate and Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, May 17 '19 at 14:54

score 0 · Answer 1 · edited May 17 '19 at 15:13

0

I'm assuming you want to leave only values on every sheet on the active workbook so this is it:

' Copies sheets 1-4
ThisWorkbook.Worksheets(Array("1", "2", "3", "4")).Copy

'Transforms each new sheet data in values only.
For Each iSheet In ActiveWorkbook.Sheets
    iSheet.UsedRange.Values = iSheet.UsedRange.Values
Next iSheet

Thought the first line copying the sheets has no sense.

edited May 17 '19 at 15:13

Tom

9,725
3
31
48

answered May 17 '19 at 14:44

Damian

5,152
1
10
21

I'm guessing you meant UsedRange.Value. Unfortunately this has the same behavior as copy and paste. If I add before the iSheet.UsedRange.Value line If iSheet.Name = "X" Then Stop End If then it enters debug mode right after the execution. If I press F8 (and nothing more, not even activate/set focus to the sheet being transformed to values) to end the loop then F5 to resume execution, it works (i.e. it keeps the series names and values in the Pivot Graph). – Leonardo Motta May 20 '19 at 11:38
If you just want a sheet to be turned into values, that is the way, you shouldn't be using `.Select` or `.Activate` because a lot of errors come from that. I don't know how did you set your `iSheet` but if it's allright that should do the trick. – Damian May 20 '19 at 11:45
UsedRange.Value = UsedRange.Value doesn't work for Pivot Tables/Charts. It still keeps the Pivot Table in place. I want to delete the Pivot Table and keep the Pivot Chart. When I delete the Pivot Table after pasting values, it keeps the Pivot Chart intact if I run the code by F8, but it alters the graph if I run with F5. My mistake, I should have made that clear. – Leonardo Motta May 20 '19 at 14:35

score 0 · Answer 2 · answered May 21 '19 at 01:38

Looks like you don't care if you really keep the values of the pivot table. In an example I just ran, I have a pivot table in G2:L19. I executed this line of code, which deleted the columns containing the pivot table, and the pivot chart remained, plotting the same data, but the data was now hard-coded into the chart.

ActiveSheet.Range("G:L").Delete

If you do want to retain the values and number formats but not the pivot table itself, this code, run in F5 or F8 mode, keeps the data where the pivot table was, and it keeps the data in the chart, hard-coded (unlinked from the pivot table).

Sub UnpivotTable()
  With ActiveSheet.Range("G2:L19")
    .Copy
    .PasteSpecial xlPasteValuesAndNumberFormats
  End With
End Sub

Do you want to keep the links from the chart to the data? Store the series formulas, undo the pivot table, and reapply the series formulas:

Sub UnpivotTableKeepChartLinks()
  Dim MyChart As Chart
  Set MyChart = ActiveSheet.ChartObjects("Chart 1").Chart
  Dim vSrsFmlas As Variant
  ReDim vSrsFmlas(1 To MyChart.SeriesCollection.Count)
  Dim iSrs As Long
  For iSrs = 1 To MyChart.SeriesCollection.Count
    vSrsFmlas(iSrs) = MyChart.SeriesCollection(iSrs).Formula
  Next
  With ActiveSheet.Range("G2:L19")
    .Copy
    .PasteSpecial xlPasteValuesAndNumberFormats
  End With
  For iSrs = 1 To MyChart.SeriesCollection.Count
    MyChart.SeriesCollection(iSrs).Formula = vSrsFmlas(iSrs)
  Next
End Sub

VBA copy paste runs different in debug mode

2 Answers2