ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Referring to a worksheet using variable (https://www.excelbanter.com/excel-programming/288315-referring-worksheet-using-variable.html)

WSF
Referring to a worksheet using variable
 
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

Ron de Bruin
Referring to a worksheet using variable
 
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)
www.rondebruin.nl



"WSF" wrote in message ...
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

WSF
Referring to a worksheet using variable
 
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" wrote in message
...
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)
www.rondebruin.nl



"WSF" wrote in message
...
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

Ron de Bruin
Referring to a worksheet using variable
 
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)
www.rondebruin.nl



"WSF" wrote in message ...
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" wrote in message
...
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)
www.rondebruin.nl



"WSF" wrote in message
...
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

WSF
Referring to a worksheet using variable
 
Thanks for your help Ron.
Regards,
WSF


"Ron de Bruin" wrote in message
...
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)
www.rondebruin.nl



"WSF" wrote in message
...
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" wrote in message
...
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)
www.rondebruin.nl



"WSF" wrote in message
...
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

Ron de Bruin
Referring to a worksheet using variable
 
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)
www.rondebruin.nl



"WSF" wrote in message ...
Thanks for your help Ron.
Regards,
WSF


"Ron de Bruin" wrote in message
...
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)
www.rondebruin.nl



"WSF" wrote in message
...
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" wrote in message
...
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)
www.rondebruin.nl



"WSF" wrote in message
...
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


All times are GMT +1. The time now is 06:56 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com