VBA: How to select currently active worksheet in my code

[Dremzy]

Hi All,

I have the code below, which takes data from the current sheet (1) creates a pivot table in sheet (2), it then creates two charts in sheet (2).

My issue is that at the end of this code I want to copy and paste a table from another sheet, lets call it "Internet statistics" back into the sheet (2). The issue is that as you will see from the code, every time this macro runs the name for the sheet where the charts are created changes.

Can someone help me make this work??

So if I was to try and do the following on sheet (2) it would not work:

Sheets("Intranet Stats").Select
    Selection.Copy
    Sheets("Sheet1").Select   <--- (It's due to the fact this changes everytime the macro below is run)
    Range("D47").Select
    ActiveSheet.Paste

Full VBA:

Sub CreatePivotTableandchart()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String
'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:F200").Address(ReferenceStyle:=xlR1C1)
'Create a new worksheet
Set sht = Sheets.Add
'Where do you want Pivot Table to start?
StartPvt = sht.Name & "!" & sht.Range("A2").Address(ReferenceStyle:=xlR1C1)
'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)
'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")
 
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Category "), "Count of Category ", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category ")
                .Orientation = xlRowField
        .Position = 1
End With
Set shtPTable = ActiveSheet
Range("A4:B11").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlPie
Application.EnableEvents = False
With ActiveSheet.PivotTables("PivotTable1")
ActiveChart.ShowAllFieldButtons = False
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.ShowPercentage = True
Selection.ShowCategoryName = False
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Category of query received into mailbox:"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Category of query received into mailbox:"
Selection.Position = xlLabelPositionOutsideEnd
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 15).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Font.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 32, 96)
        .Transparency = 0
        .Solid
    End With
    With Selection.Format.TextFrame2.TextRange.Font
        .NameComplexScript = "Arial"
        .NameFarEast = "Arial"
        .Name = "Arial"
    End With
    Selection.Format.TextFrame2.TextRange.Font.Size = 14
ActiveChart.SeriesCollection(1).Points(1).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 36, 105)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(2).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(158, 162, 162)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(3).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(205, 0, 88)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(5).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 169, 206)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(1).Points(6).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(240, 179, 35)
.Transparency = 0
.Solid
End With
With ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Position = xlLabelPositionOutsideEnd
End With
Range("A1").Select
ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Legend.Select
    ActiveChart.ChartArea.Select
    ActiveChart.ChartArea.Copy
    Range("M14").Select
    ActiveSheet.Pictures.Paste.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Delete
 
Range("A5").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Category "). _
        Orientation = xlHidden
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time taken to respond" _
)
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Time taken to respond"), _
        "Count of Time taken to respond", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Time taken to respond" _
        )
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Category ").Orientation = _
        xlHidden
Set shtPTable = ActiveSheet
Range("A4:B11").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.HasTitle = True
    
    ' Formatting
    ActiveChart.ChartTitle.Text = " Average response time to mailbox query:"
    ActiveChart.ChartTitle.Font.Size = 10
    ActiveChart.SetSourceData Source:=Range("$A$2:$B$6")
    ActiveChart.ApplyLayout (1)
    ActiveChart.ShowAllFieldButtons = False
    ActiveChart.SeriesCollection(1).Interior.Color = RGB(0, 36, 105)
    ActiveChart.ChartTitle.Font.Color = RGB(0, 36, 105)
    ActiveChart.HasLegend = False
    With ActiveChart.Axes(xlValue).TickLabels.Font
        .Size = 10
        .Name = "Arial"
        .Color = RGB(0, 36, 105)
    End With
    With ActiveChart.Axes(xlCategory).TickLabels.Font
        .Size = 10
        .Name = "Arial"
        .Color = RGB(0, 36, 105)
    End With
  
    ActiveChart.ChartArea.Select
    ActiveChart.ChartArea.Copy
    Range("M33").Select
    ActiveSheet.Pictures.Paste.Select
    Range("M13:T51").Select
    Application.CutCopyMode = False
    Selection.Cut
    Range("D8").Select
    ActiveSheet.Paste
ActiveSheet.ChartObjects(1).Activate
ActiveSheet.ChartObjects(1).Delete
Application.ScreenUpdating = True
Application.EnableEvents = True

    Sheets("Intranet Stats").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("D47").Select
    ActiveSheet.Paste
End Sub

 

[Dremzy]

I have a question which may be an easy way to resolve this issue. Is there a way to give a specific name to the sheet that the charts/pivot table are created in, that way I can reference it in my other macro???