VBA creating a new tab help

[khalk]

Hey guys first post, sorry for any transgressions. So, I'm trying to copy the results from a pivot table to a new tab that is created and named by the macro. The problem is coming with the creation and naming of the tab. After the first run it returns with an out of range function. I understand this is because when I recorded the macro it set the new tab as "Sheet2" and the new sheets are going to be "sheets3"etc. My seconded question is it possible to name a new tab from the contents from a cell in the pivot table. Is this possible?

Here is what I have so far.

Sub Attempt1()
'
' Attempt1 Macro
'

'
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Questions").Select
    Range("B2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "ATM"
    Sheets("Questions").Select
    Range("A4:C6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("ATM").Select
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Questions").Select
    Application.CutCopyMode = False
    Range("C5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
End Sub

 

[thecrazyjogger]

Dear khalk,

I would suggest that you name your new sheet immediately so you can refer back to it easily, using any cell you want. For example, the below code will name your new sheet to whatever is in Range A1 of your Questions sheet.
Put this after your first line of code "Sheet.Add"

ActiveSheet.Name = Sheets("Questions").Range("A1").Value

 

[RoryA]

On the assumption you only have one pivot table on the Questions sheet and you want to copy and then clear it, I'd suggest something like this:

Sub Attempt1()

    Dim ws                    As Worksheet
    Dim PT                    As PivotTable

    Set PT = Sheets("Questions").PivotTables(1)

    Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
    ws.Name = "ATM"
    PT.TableRange2.Copy
    ws.Range("C1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                SkipBlanks:=False, Transpose:=False
    PT.TableRange2.Clear
End Sub

 

[khalk]

Thanks guys The both worked out perfectly!