vba - Lookup Worksheet Name In Cell

[ClwnMan76]

I'm an idiot or something. I took, what I thought were pretty straight forward directions on here, but it's not working out for me. MISC S1 has a month in it. I get an error no matter what I do with the "Set ws = Sheets(MySheet)" line. I just want that "JUL" to change depending on the cell in S1.

Sub ImportSixtySecond()   Dim Fname As String
   Dim SrcWbk As Workbook
   Dim DestWbk As Workbook
   Dim ws As Worksheet
   Dim MySheet As String
   Set DestWbk = ThisWorkbook
 
   MySheet = Sheets("MISC").Range("s1").Value
    Set ws = Sheets(MySheet)
     
   Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
   If Fname = "False" Then Exit Sub
   Set SrcWbk = Workbooks.Open(Fname)
   
  Dim answer As Integer
  answer = MsgBox("Did You Pick The Correct File?", vbQuestion + vbYesNo)
 
  If answer = vbNo Then Exit Sub


   MsgBox "Your Screen APR Look Frozen While Importing." & vbNewLine & vbNewLine & "Just Be Patient!", vbOKCancel + vbQuestion


SrcWbk.Sheets("JUL").Range("b4:ai18").Copy: DestWbk.Sheets("62nd").Range("b4").PasteSpecial xlPasteValuesAndNumberFormats

 

[Steve_]

Does your Workbook contain a sheet by the EXACT name of what is being referred to in s1 on your sheet named MISC?

 

[JustynaMK]

Seconded, the code looks correct to me and it also works fine on my side. Check your Sheet name and S1 cell value.

 

[Fluff]

Does S1 contain "JUL" or does it contain a date that is formatted to show "JUL"

 

[ClwnMan76]

It does say JUL, but it's pulling it through a formula on another cell. I can post the workbook if you so wish.

 

[ClwnMan76]

Does your Workbook contain a sheet by the EXACT name of what is being referred to in s1 on your sheet named MISC?

Yes it does.

 

[ClwnMan76]

The only other thing is the destination destination's sheets are being named dynamically through cell reference.

 

[Fluff]

What is the formula in S1?

 

[ClwnMan76]

Thanks for all your help. I finally figured it out. My best guess is that I wasn't being specific on which workbook had the info I wanted to reference.

 

[Fluff]

Glad you sorted it & thanks for the feedback