Copying and pasting between workbook errors

[nikifi]

Hello,
I have a situation that I am hoping a VBA guru can help me with. I am trying to copy some information from one workbook to the next. The thing is, in the spreadsheet we are copying it from, the column I want to copy varies. Sometimes it is column B sometimes it is C etc. this is because the spreadsheet is extracted from a tool, and the person extracting it selects what column they want to extract. Some people may choose 4 columns, some 5 etc, depending on what they want to see. Also the name of the worksheet is named dynamically based on the user name of the person who is exporting the document. For my code though, I couldnt figure out how to dynamically get the sheet name, so I called it "test_name" and renamed the sheet accordingly. In my example the sheet I am copying it from is called "ngpf_file", the sheet I am copying it to is called review_tracking. The code lives in the workbook with the sheet called "review_tracking", hence when I am trying to paste I referred to "This_workbook". I had this work before, now it's not, it's giving me a subscript out of range error when I am trying to paste. Also, I tried to dynamically grab all the records on the column, as it varies, then I converted the column number to the alphabatic value. I am sure this code can be cleaned up tremendously. I am definitely not a programmer, but I know there must be cleaner ways to do things. i just get snippets of code from online and try to put them together. Any help is appreciated. thanks

Sub LookInOtherBookCSCRValues()
Dim wBook As Workbook
Dim wSheet As Worksheet
Dim test1 As String
'On Error Resume Next
If Workbooks("ngpf_file.xls") Is Nothing Then
Workbooks.Open Filename:="C:\Documents and Settings\m308312\Desktop\ng\ngpf_file.xls"
End If
Set wBook = Workbooks("ngpf_file.xls")
    'On Error Resume Next
    For Each wSheet In wBook.Worksheets
'Finds the column from ngpf_file with heading "ID"
        strGotIt = wSheet.Rows(1).Find(What:="ID", After:=wSheet.Cells(1, 1), _
                   LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                   SearchDirection:=xlNext, MatchCase:=True).Column
        'Convert Column number value to Letter
        If strGotIt > 26 Then
        MyColumnLetter = Chr(Int((strGotIt - 1) / 26) + 64) & Chr(((strGotIt - 1) Mod 26) + 65)
        Else
        MyColumnLetter = Chr(strGotIt + 64)
        End If
        
        
                   
        If strGotIt <> vbNullString Then
           
            FinalRow = Range(MyColumnLetter & 65536).End(xlUp).Row
            wBook.Activate
            wBook.Worksheets("testSheet").Range(MyColumnLetter & 2, Range(MyColumnLetter & 2, MyColumnLetter & FinalRow)).Select
            'ThisWorkbook.Worksheets("Review_Tracking").Range("A3").PasteSpecial Paste:=xlPasteValues
                              
             
             
          
            Else
            
            MsgBox "The Value 'ID' has not been found in" & wBook
                      
        End If
        
        Next
   
        
        End Sub

 

[nikifi]

Clarification in my description. The name of the WORKBOOK I am copying it from is called ngpf_file, the name of the sheet in the same workbook is called test_sheet. the name of the workbook I am pasting the value is "ThisWorkbook" and the name of the sheet in this workbook is called "review_tracking"