Creating macro to create 2 worksheets from a pivot table and move them to a new workbook

Hi there. First time poster. I'm having some trouble.



I am trying to save myself from needless busy work by creating a macro. I am starting with a workbook that contains 2 pivot tables created from 2 large data fields. I need to create individual tables from this for each individual person in these data sets. I have been trying to create a macro that creates a new empty workbook, then creates the sheet I need from the first pivot, moves the sheet to the new workbook, renames the sheet, creates the sheet I need from the second pivot, moves the second sheet to the new workbook, renames the second sheet. And I need the macro to be relative so when I try to duplicate for each of the people lower on the list everything carries over appropriately.


The process in excel looks like this:

File->new->workbook

Active workbook->first pivot table->double click cell J5

right-click new sheet->move or copy->select new workbook

right-click new sheet now in new workbook->rename->"payments"

return to first active workbook->second pivot table->double click cell J5

right-click second new sheet->move or copy->select new workbook

right-click second new sheet now in new workbook->rename->"payments 2"


The cell J5 needs to be relative, so that the macro will work for J6, J7, etc.


The macro I've been trying to create myself (I have no VBA experience at all):

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+o
'
    Workbooks.Add
    Windows("Book1 test.xlsm").Activate
    Range("J5").Select
    Selection.ShowDetail = True
    Sheets("Sheet1").Select
    Sheets("Sheet1").Move Before:=Workbooks("Book3").Sheets(1)
    Windows("Book1 test.xlsm").Activate
    Sheets("NSF Pivot").Select
    Range("J5").Select
    Selection.ShowDetail = True
    Sheets("Sheet2").Select
    Sheets("Sheet2").Move Before:=Workbooks("Book3").Sheets(1)
    Sheets("Sheet1 (2)").Select
    Sheets("Sheet1 (2)").Name = "payments"
    Sheets("Sheet2 (2)").Select
    Sheets("Sheet2 (2)").Name = "payments 2"
End Sub

this results in runtime error '9'; subscript out of range

I also have the 'use relative references' selected, and I'm using excel 2007 if it matters.

Any help/advice/insight you can provide would be greatly appreciated. It seemed so easy to do this at first, but now I've been banging my head against this solution for far too long. Help me internets, you're my only hope.