open variably named file, copy/paste data into consolidation file, open next file in list

Hello,

This is my first post, and I know enough about macros/VBA to be dangerous (i.e. write simple macros) but not enough to use variables, named ranges, etc. effectively!

I have a set of files, each of which is similarly named, and I want to open one, copy data from a pivot table on a tab that is identically named in each file, and paste the data into a "consolidation" file from which I'm running the macro, close the file and open next one.

I listed the variable part of the file name in one column of the “consolidation” file, and I think I have the code right to open a file, copy the data, but my issues are:
1) I get an error selecting the named range (a variable) in which to paste the data, (at this point I get the error “Run-time error ’1004’: Method ‘Range’ of object ‘_Global’ failed”
2) switching back and forth between the "consolidation" file and the file with the data appears to work, but sometimes the data file opens yet does not become the active workbook, and that makes the rest of the code not work.

Any help you can offer would be greatly appreciated!

Thanks,
Stacey

Sub Consolidate_Data()
'
' open file with variable part of name listed in a1 of sheet1
' paste data from pivot table into a1 of sheet2, close file, reset variables and run through counter

    Dim i As Integer
    Dim sFileName As String
    Dim rFileList As Range
    Dim rPasteDataHere As Range
    Dim wbConsol As Workbook
    Dim wbTemp As Workbook
        
    'naming the consolidation workbook so I can switch back to it
    Set wbConsol = ActiveWorkbook
    
    'identify the starting point for the list of file names, set first file name string
    Sheets("Sheet1").Activate
    Range("a1").Select
    Set rFileList = ActiveCell
    sFileName = ActiveCell.Value
    
    'identify where to paste the data once the first file is opened
    Sheets("Sheet2").Activate
    Range("a1").Select
    Set rPasteDataHere = ActiveCell
    
    For i = 1 To 12
    
    'there are 12 files to go through - open data file, select pivot table, copy data back into "consolidation" file
    Workbooks.Open Filename:="G:\Planning\FY2012-2013 " & sFileName & ".xlsm", UpdateLinks:=0
    'sometimes the workbook opens in the background and does not become the active workbook
    
    Set wbTemp = ActiveWorkbook
    ActiveWorkbook.Sheets("Pivot Tables").PivotTables("Total Pivot").TableRange1.Select
    Selection.Copy
    wbConsol.Activate
    Range("rPasteDataHere").Select 'I'm getting the following error: Run-time error '1004':  Method 'Range' of object '_Global' failed
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'go to end of column plus down one row and reset rPasteDatahere
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Set rPasteDataHere = ActiveCell
    
    'close the data file
    wbTemp.Activate
    ActiveWindow.Close False
    
    'go back to start of file list, move down one and reset rFileList and sFileName
    Range("rFileList").Select
    ActiveCell.Offset(1, 0).Select
    Set rFileList = ActiveCell
    sFileName = ActiveCell.Value
    
    Next i
End Sub