Referring to a worksheet using variable

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Excel 8.0
I am testing a means of identifying / selecting (and then copying) a
worksheet within the current workbook.
The report sheets use a standard naming format - Report Name - Mmm-YY e.g.
"Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out Report -
Dec-03" etc etc
I am asking the code to confirm the sheets existence.

Sub CheckReport()
Dim ReportSheetMonth As String, ReportSheetType as String, ReportSheetName
as String

'Users enter the Month Required
ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the
reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04",
"Report Period"))
' I have other code here that checks the validity of the entry. An example
of an entry would be say "Dec-03"

ReportSheetType = "Sales Report - "
ReportSheetName = ReportSheetType & ReportSheetMonth

If WorksheetExists(ReportSheetName) = True Then _ ' see below
GoTo ReportExists
MsgBox "Report does NOT exist" ' do what is required as a result
Exit Sub

TatReportExists:
MsgBox "Report does exist" ' do what is required as a result
Exit Sub

To test for the worksheet's existence I use the following Function.

Public Function WorksheetExists(WSName As String, Optional WB As Workbook =
Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook,
WB).Worksheets(WSName).Name))

End Function

The above does not work.

But if I paste the actual name of the worksheet (eg Sales Report - Dec-03)
directly into the code above
i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc

it does work.

What am I doing wrong here?

Any help gratefully appreciated.

WSF
 
Hi

The only thing I see is that you use this line

GoTo ReportExists

But he can't find that because the name = TatReportExists

TatReportExists:
MsgBox "Report does exist" ' do what is required as a result
Exit Sub
 
Hello Ron,
That is a typo on my part. I have correct the error and still no go.
The issue is why the code will accept a text string but not my variable.

Regards,
Bill Fraser


Ron de Bruin said:
Hi

The only thing I see is that you use this line

GoTo ReportExists

But he can't find that because the name = TatReportExists

TatReportExists:
MsgBox "Report does exist" ' do what is required as a result
Exit Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Excel 8.0
I am testing a means of identifying / selecting (and then copying) a
worksheet within the current workbook.
The report sheets use a standard naming format - Report Name - Mmm-YY e.g.
"Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out Report -
Dec-03" etc etc
I am asking the code to confirm the sheets existence.

Sub CheckReport()
Dim ReportSheetMonth As String, ReportSheetType as String, ReportSheetName
as String

'Users enter the Month Required
ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the
reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04",
"Report Period"))
' I have other code here that checks the validity of the entry. An example
of an entry would be say "Dec-03"

ReportSheetType = "Sales Report - "
ReportSheetName = ReportSheetType & ReportSheetMonth

If WorksheetExists(ReportSheetName) = True Then _ ' see below
GoTo ReportExists
MsgBox "Report does NOT exist" ' do what is required as a result
Exit Sub

TatReportExists:
MsgBox "Report does exist" ' do what is required as a result
Exit Sub

To test for the worksheet's existence I use the following Function.

Public Function WorksheetExists(WSName As String, Optional WB As Workbook =
Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook,
WB).Worksheets(WSName).Name))

End Function

The above does not work.

But if I paste the actual name of the worksheet (eg Sales Report - Dec-03)
directly into the code above
i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc

it does work.

What am I doing wrong here?

Any help gratefully appreciated.

WSF
Click to expand...
Click to expand...
 
Hi Bill

This is working for me

Sub CheckReport()
Dim ReportSheetMonth As String, ReportSheetType As String, ReportSheetName As String

ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the reports you wish to check [mmm-yy]" & Chr$(13) &
Chr$(13) _
& "eg Mar-04", "Report Period"))

ReportSheetType = "Sales Report - "
ReportSheetName = ReportSheetType & ReportSheetMonth

If WorksheetExists(ReportSheetName) = True Then
MsgBox "Report does exist"
Else
MsgBox "Report does NOT exist"
Exit Sub
End If
End Sub

Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name))
End Function


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




WSF said:
Hello Ron,
That is a typo on my part. I have correct the error and still no go.
The issue is why the code will accept a text string but not my variable.

Regards,
Bill Fraser


Ron de Bruin said:
Hi

The only thing I see is that you use this line

GoTo ReportExists

But he can't find that because the name = TatReportExists

TatReportExists:
MsgBox "Report does exist" ' do what is required as a result
Exit Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Excel 8.0
I am testing a means of identifying / selecting (and then copying) a
worksheet within the current workbook.
The report sheets use a standard naming format - Report Name - Mmm-YY e.g.
"Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out Report -
Dec-03" etc etc
I am asking the code to confirm the sheets existence.

Sub CheckReport()
Dim ReportSheetMonth As String, ReportSheetType as String, ReportSheetName
as String

'Users enter the Month Required
ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the
reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04",
"Report Period"))
' I have other code here that checks the validity of the entry. An example
of an entry would be say "Dec-03"

ReportSheetType = "Sales Report - "
ReportSheetName = ReportSheetType & ReportSheetMonth

If WorksheetExists(ReportSheetName) = True Then _ ' see below
GoTo ReportExists
MsgBox "Report does NOT exist" ' do what is required as a result
Exit Sub

TatReportExists:
MsgBox "Report does exist" ' do what is required as a result
Exit Sub

To test for the worksheet's existence I use the following Function.

Public Function WorksheetExists(WSName As String, Optional WB As Workbook =
Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook,
WB).Worksheets(WSName).Name))

End Function

The above does not work.

But if I paste the actual name of the worksheet (eg Sales Report - Dec-03)
directly into the code above
i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc

it does work.

What am I doing wrong here?

Any help gratefully appreciated.

WSF
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help Ron.
Regards,
WSF


Ron de Bruin said:
Hi Bill

This is working for me

Sub CheckReport()
Dim ReportSheetMonth As String, ReportSheetType As String, ReportSheetName As String

ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the
Click to expand...
reports you wish to check [mmm-yy]" & Chr$(13) &
Chr$(13) _
& "eg Mar-04", "Report Period"))

ReportSheetType = "Sales Report - "
ReportSheetName = ReportSheetType & ReportSheetMonth

If WorksheetExists(ReportSheetName) = True Then
MsgBox "Report does exist"
Else
MsgBox "Report does NOT exist"
Exit Sub
End If
End Sub

Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name))
End Function


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Hello Ron,
That is a typo on my part. I have correct the error and still no go.
The issue is why the code will accept a text string but not my variable.

Regards,
Bill Fraser


Ron de Bruin said:
Hi

The only thing I see is that you use this line

GoTo ReportExists

But he can't find that because the name = TatReportExists

TatReportExists:
MsgBox "Report does exist" ' do what is required as a result
Exit Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Excel 8.0
I am testing a means of identifying / selecting (and then copying) a
worksheet within the current workbook.
The report sheets use a standard naming format - Report Name -
Click to expand...
Mmm-YY
e.g.
"Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out Report -
Dec-03" etc etc
I am asking the code to confirm the sheets existence.

Sub CheckReport()
Dim ReportSheetMonth As String, ReportSheetType as String, ReportSheetName
as String

'Users enter the Month Required
ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the
reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04",
"Report Period"))
' I have other code here that checks the validity of the entry. An example
of an entry would be say "Dec-03"

ReportSheetType = "Sales Report - "
ReportSheetName = ReportSheetType & ReportSheetMonth

If WorksheetExists(ReportSheetName) = True Then _ ' see below
GoTo ReportExists
MsgBox "Report does NOT exist" ' do what is required as a result
Exit Sub

TatReportExists:
MsgBox "Report does exist" ' do what is required as a result
Exit Sub

To test for the worksheet's existence I use the following Function.

Public Function WorksheetExists(WSName As String, Optional WB As Workbook =
Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook,
WB).Worksheets(WSName).Name))

End Function

The above does not work.

But if I paste the actual name of the worksheet (eg Sales Report - Dec-03)
directly into the code above
i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc

it does work.

What am I doing wrong here?

Any help gratefully appreciated.

WSF
Click to expand...
Click to expand...
Click to expand...
 
Hi WSF

I see I did not remove the Exit sub in your macro



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




WSF said:
Thanks for your help Ron.
Regards,
WSF


Ron de Bruin said:
Hi Bill

This is working for me

Sub CheckReport()
Dim ReportSheetMonth As String, ReportSheetType As String, ReportSheetName As String

ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the
Click to expand...
reports you wish to check [mmm-yy]" & Chr$(13) &
Chr$(13) _
& "eg Mar-04", "Report Period"))

ReportSheetType = "Sales Report - "
ReportSheetName = ReportSheetType & ReportSheetMonth

If WorksheetExists(ReportSheetName) = True Then
MsgBox "Report does exist"
Else
MsgBox "Report does NOT exist"
Exit Sub
End If
End Sub

Public Function WorksheetExists(WSName As String, Optional WB As Workbook = Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB).Worksheets(WSName).Name))
End Function


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Hello Ron,
That is a typo on my part. I have correct the error and still no go.
The issue is why the code will accept a text string but not my variable.

Regards,
Bill Fraser


Hi

The only thing I see is that you use this line

GoTo ReportExists

But he can't find that because the name = TatReportExists

TatReportExists:
MsgBox "Report does exist" ' do what is required as a result
Exit Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Excel 8.0
I am testing a means of identifying / selecting (and then copying) a
worksheet within the current workbook.
The report sheets use a standard naming format - Report Name - Mmm-YY
e.g.
"Sales Report - Dec-03", "Orders In Report - Dec-03", "Orders Out
Report -
Dec-03" etc etc
I am asking the code to confirm the sheets existence.

Sub CheckReport()
Dim ReportSheetMonth As String, ReportSheetType as String,
ReportSheetName
as String

'Users enter the Month Required
ReportSheetMonth = UCase(InputBox("Please enter the Month-Year of the
reports you wish to check [mmm-yy]" & Chr$(13) & Chr$(13) & "eg Mar-04",
"Report Period"))
' I have other code here that checks the validity of the entry. An
example
of an entry would be say "Dec-03"

ReportSheetType = "Sales Report - "
ReportSheetName = ReportSheetType & ReportSheetMonth

If WorksheetExists(ReportSheetName) = True Then _ ' see below
GoTo ReportExists
MsgBox "Report does NOT exist" ' do what is required as a result
Exit Sub

TatReportExists:
MsgBox "Report does exist" ' do what is required as a
result
Exit Sub

To test for the worksheet's existence I use the following Function.

Public Function WorksheetExists(WSName As String, Optional WB As
Workbook =
Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook,
WB).Worksheets(WSName).Name))

End Function

The above does not work.

But if I paste the actual name of the worksheet (eg Sales Report -
Dec-03)
directly into the code above
i.e. If WorksheetExists("Sales Report - Dec-03") = True Then etc etc

it does work.

What am I doing wrong here?

Any help gratefully appreciated.

WSF
Click to expand...
Click to expand...
Click to expand...
 
Back
Top