Use cell value (text) to select sheet in different workbook.

[jjsauer]

With help from Wigi, I have a nice little chunk of code that does some work for me. It utilizes two workbooks. One is like the master file, the second is a data file with 15 sheets or so. I am trying to keep from copying and pasting by allowing the code to pick up the sheet name (which is used on the master list) in stead of repeating the code a 15 times with a different sheet name.

Make sense? I hope.

Here is the beginning of my code (the red is where I'm stuck):

Dim rCell As Range
Dim mysheet As Excel.Sheets


Windows("FY12-Q3 Value Tracker Key Measure Summary Tables FINAL.xlsm").Activate
Sheets("Store Level Value").Select
'Total Respondents, NBP-PBP-NBQ-PBQ

For Each rCell In Range("B4:B10").Cells
Set mysheet = rCell.Offset(0, -1).Value
With Workbooks("FY12-Q3 Data Tables.xlsx").Sheets(mysheet).Columns(1)
Set c = .Find("[Q6]", LookIn:=xlValues)
Set c = .Find(rCell, After:=c)
Set c = .Find("Bottom 2 (NET)", After:=c)
End With
rCell.Offset(0, 11).Value = c.Offset(, 14).Value
rCell.Offset(0, 20).Value = c.Offset(, 3).Value
rCell.Offset(0, 21).Value = c.Offset(, 12).Value
rCell.Offset(0, 22).Value = c.Offset(, 15).Value
Next


Thanks in advance!

 

[cmefly]

I think it may have to do with the way you defined your variable....

try dim mysheets as worksheet

 

[jjsauer]

cmefly,

I had previously tried that, but forgot to switch it back. I agree it seems closer to what I need.

In the first red line I am getting an error: Object Required message right now...

 

[cmefly]

mysheet needs to be set as a range...

 

[jjsauer]

still not working. I think part of the problem is the rCell is in a different workbook.

 

[jjsauer]

bump

 

[jjsauer]

bump