Working with multiple workbooks using VBA

[JennV]

Hello,

I currently have this code:

Dim FileName As String
FileName = Sheets("Tracker").Range("A4")

Dim FileName1 As String
FileName1 = Sheets("Tracker").Range("A5")

Set wB2 = Workbooks.Open("file location")
If wB2.ReadOnly Then
ActiveWorkbook.Close
MsgBox "Cannot update as someone is currently updating. Please try again."

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub
End If

Sheets(FileName).Select
Cells.Find(What:=FileName1, After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows).Offset(1, 0).Select
ActiveSheet.Paste


I keep getting a 'subscript out of range' error when it hits the red text above. My guess is because it's not understanding that I'm trying to reference the other workbook (wb1).

What I have in wb1 in sheet 'Tracker' is:
A4: EDM12345
A5: John Smith

So after I copy data from wb1, I want to use the value in A4 to select the sheet EDM12345 in wb2. In addition, it look for A5 (John Smith) within sheet EDM12345 in wb2.

I could do this:

Sheets("EDM12345").Select
Cells.Find(What:="John Smith", After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows).Offset(1, 0).Select
ActiveSheet.Paste

But since wb2 is a database and there will be multiple wb1 with different search criterias (instead of John Smith, say I want to look for Jane Doe or instead of EDM12345, I want to go into sheet EDM54321), I would like to save the hassle of going into wb1 to change the VBA coding.

Any help is much appreciated.

 

[footoo]

At the start of your code, is wb1 the active workbook ?
Check the actual values being assigned to FileName and FileName1 ?
For debugging purposes, don't set DisplayAlerts to False - so you can see if any problems

 

[Jon Peltier]

You probably want Workbooks(FileName), not Sheets(FileName).

 

[footoo]

You probably want Workbooks(FileName), not Sheets(FileName).

According to the info supplied, FileName is a worksheet name, not a workbook name.

 

[JennV]

According to the info supplied, FileName is a worksheet name, not a workbook name.

Thank you! I have resolved the issue. What I didn't do is set the active workbook (wb1) at the beginning of the code so that's why I was getting the error. Have a good day