vba - Lookup Worksheet Name In Cell

ClwnMan76

New Member
Joined
Oct 26, 2009
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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. :oops:

Code:
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Does your Workbook contain a sheet by the EXACT name of what is being referred to in s1 on your sheet named MISC?
 
Upvote 0
Seconded, the code looks correct to me and it also works fine on my side. Check your Sheet name and S1 cell value.
 
Upvote 0
Does S1 contain "JUL" or does it contain a date that is formatted to show "JUL"
 
Upvote 0
It does say JUL, but it's pulling it through a formula on another cell. I can post the workbook if you so wish.
 
Upvote 0
The only other thing is the destination destination's sheets are being named dynamically through cell reference.
 
Upvote 0
What is the formula in S1?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top