Storing a worksheet name for use in another sub

Question

I'm using one sub-procedure to store filepaths/workbooks and worksheets in Public variables which can then be accessed by other sub procedures in the module. Here is some example code:

Public myfp As String
Public mywb As Workbook
Public myws As Worksheet

Sub FilePaths()
    myfp = Application.GetOpenFileName(Title:="Please select the file.")
    Set mywb = Workbooks.Open(myfp, 0)
    If Left(mywb.Sheets(1).Name, 2) = "01" Then
        Set myws = mywb.Sheets(1)
    Else
        Set myws = mywb.Sheets(2)
    End If
    mywb.Saved = True
    mywb.Close
Exit Sub

Sub Stuff()
    Dim wb As WorkBook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = wb.Sheets(1)
    ws.Range("A1").Value = wb.ws.Range("A1").Value
End Sub

So my first sub determines which sheet I need and my second sub should put the value of this sheets cell into my sheet. However all I get is errors. I figure this may be because I have to open the file so I tried something I know should work:

SUb Stuff()
    Range("A1").Formula = "='[" & myfp & "]" & myws & "'!A1"
End Sub

However this also doesn't work. What am I doing wrong?

score 1 · Answer 1 · answered Aug 23 '13 at 14:24

1

Remove the wb qualifier from in front of your ws object.

Change this:

ws.Range("A1").Value = wb.ws.Range("A1").Value

To this:

ws.Range("A1").Value = ws.Range("A1").Value

answered Aug 23 '13 at 14:24

Jon Crowell

21,695
14
89
110

score 1 · Answer 2 · answered Aug 23 '13 at 14:24

1

In the first Stuff, once ws has been Set, use: something = ws.Range("A1").Value

you don't need wb.ws

In the second Stuff, you need to use a string variable in creating a Formula, myws.Name rather than myws.

answered Aug 23 '13 at 14:24

Gary's Student

95,722
10
59
99

score 1 · Accepted Answer · answered Aug 23 '13 at 14:26

In your test example, you are using the wb and ws objects, not their names. Try this:

Public myfp As String
Public myWbName As String
Public myWsName As String

Sub FilePaths()
Dim myWb As Workbook

myfp = Application.GetOpenFilename(Title:="Please select the file.")
Set myWb = Workbooks.Open(myfp, 0)
myWbName = myWb.Name
If Left(myWb.Sheets(1).Name, 2) = "01" Then
    myWsName = myWb.Sheets(1)
Else
    myWsName = myWb.Sheets(2).Name
End If
myWb.Saved = True
myWb.Close
End Sub

Sub Stuff()
    Range("A1").Formula = "='[" & myfp & "]" & myWsName & "'!A1"
End Sub

This seems to only partly work, using that bottom 'stuff' sub, I get this in the formula bar `='[C:\MacroFiles\Files\[My Long File Name 20 08 2013.xlsx]01Sheet]My Long File Name 2'!A1` so for some reason it seems to be mucking up and I have no idea why? — Rapid, Aug 27 '13 at 08:02

Storing a worksheet name for use in another sub

3 Answers3