VBA to select a cell as the variable for a lookup

[NateD1]

Hi All,
i have the below code which pulls data from another closed workbook by looking up row no. and col no. and copys data across

can someone help me by changing the code so that the Bold/underlined (36) part of the code is linked to a cell (A1 in the file the data is copied to), which i can then change each time to pull from a different row in the closed work book.

Sub Import()
Application.ScreenUpdating = False
Dim myfilename, strWsName
Dim a As Variant
Dim wb As Workbook
    myfilename = "z:\xxxx\Reports\xxxxx\filename.xlsm" 
    strWsName = ActiveSheet.Range("B1")
Set wb = Workbooks.Open(myfilename)
    With wb.Sheets(strWsName)
        a = .Cells(31, 36).Resize(29) ' Select Row No, col No, number of cells to copy
    End With
    wb.Close False
    Cells(5, 3).Resize(UBound(a)) = a 'Select location to paste to , Row No, col No
      MsgBox "Imported"
      Application.ScreenUpdating = True

 

[Logit]

a = .Cells(0,1)

 

[NateD1]

a = .Cells(0,1)

sorry to confuse, but i need the no.36 in the code to be linked to a Cell, so example, when i change cell A1, to 3, the code will change the no.36 to no.3 to lookup different column when pulling data

 

[Logit]

Hmmm ...

Try :

a = .Cells(31, Range("A1"))

 

[NateD1]

Hmmm ...

Try :

a = .Cells(31, Range("A1"))

thanks it worked!, i added: .Cells(31, Sheet1.Range("A1"))

 

[Logit]

Great !