Can't concatenate
 

What's wrong with this:

Dim SrcWksht As Worksheet
Dim WkshtMon As Worksheet

Set SrcWksht = ThisWorkbook("Sheet1")

wkshtmon.Name=srcwksht.name&" - Monthly"<---Compile error - expected
end of statement

Thanks.

Can't concatenate
 

You need code like

Set SrcWksht = ThisWorkbook.Worksheets("Sheet1")
wkshtmon.Name=srcwksht.name & " - Monthly"

Note the space before the & character. Without the space, VBA
treats the & character as a data type conversion operator.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"davegb" wrote in message
oups.com...
What's wrong with this:

Dim SrcWksht As Worksheet
Dim WkshtMon As Worksheet

Set SrcWksht = ThisWorkbook("Sheet1")

wkshtmon.Name=srcwksht.name&" - Monthly"<---Compile error -
expected
end of statement

Thanks.

Can't concatenate
 

Thanks, Chip!

Can't concatenate
 

I fixed it, but when I run it, I get an "Object variable or with block
variable not set" error.

Sub CountMonth()
Dim CountArray(1 To 12, 1 To 7)
Dim SrcWksht As Worksheet
'Dim SrcWkshtName As String
Dim MonWksht As Worksheet
Dim strCntctDate As String
Dim strCntctMo As String


Set SrcWksht = ThisWorkbook.Sheets("Sheet1")

MonWksht.Name = SrcWksht.Name & " - Monthly"<---error

Any suggestions?

Can't concatenate
 

Sure,
you haven't set MonWksht to anything.

So you have an object variable undefined/Not Set.

--
Regards,
Tom Ogilvy
"davegb" wrote in message
oups.com...
I fixed it, but when I run it, I get an "Object variable or with block
variable not set" error.

Sub CountMonth()
Dim CountArray(1 To 12, 1 To 7)
Dim SrcWksht As Worksheet
'Dim SrcWkshtName As String
Dim MonWksht As Worksheet
Dim strCntctDate As String
Dim strCntctMo As String


Set SrcWksht = ThisWorkbook.Sheets("Sheet1")

MonWksht.Name = SrcWksht.Name & " - Monthly"<---error

Any suggestions?

Can't concatenate
 

Tom Ogilvy wrote:
Sure,
you haven't set MonWksht to anything.

So you have an object variable undefined/Not Set.

--
Regards,
Tom Ogilvy

Thanks, Tom. So I can't determine a worksheet by setting it's name
based on another worksheet? Would it work if I set Monwksht to some
other sheet, like Sheet1, then changed it to SrcWksht.Name + "Monthly"?
I don't want to have to input the second sheet name directly. I want it
to be the source sheet name + "monthly". Is there a way to do this?


"davegb" wrote in message
oups.com...
I fixed it, but when I run it, I get an "Object variable or with block
variable not set" error.

Sub CountMonth()
Dim CountArray(1 To 12, 1 To 7)
Dim SrcWksht As Worksheet
'Dim SrcWkshtName As String
Dim MonWksht As Worksheet
Dim strCntctDate As String
Dim strCntctMo As String


Set SrcWksht = ThisWorkbook.Sheets("Sheet1")

MonWksht.Name = SrcWksht.Name & " - Monthly"<---error

Any suggestions?

Can't concatenate
 

Your code says

set the name of some unknown worksheet to have the name of "Sheet1" & " -
Monthly"

Some way you have to establish which worksheet you want to rename.

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ups.com...

Tom Ogilvy wrote:
Sure,
you haven't set MonWksht to anything.

So you have an object variable undefined/Not Set.

--
Regards,
Tom Ogilvy

Thanks, Tom. So I can't determine a worksheet by setting it's name
based on another worksheet? Would it work if I set Monwksht to some
other sheet, like Sheet1, then changed it to SrcWksht.Name + "Monthly"?
I don't want to have to input the second sheet name directly. I want it
to be the source sheet name + "monthly". Is there a way to do this?


"davegb" wrote in message
oups.com...
I fixed it, but when I run it, I get an "Object variable or with block
variable not set" error.

Sub CountMonth()
Dim CountArray(1 To 12, 1 To 7)
Dim SrcWksht As Worksheet
'Dim SrcWkshtName As String
Dim MonWksht As Worksheet
Dim strCntctDate As String
Dim strCntctMo As String


Set SrcWksht = ThisWorkbook.Sheets("Sheet1")

MonWksht.Name = SrcWksht.Name & " - Monthly"<---error

Any suggestions?

Can't concatenate
 

something like this won't work for you

Option Explicit
Sub test()
Dim SrcWksht As Worksheet
Dim WkshtMon As String

Set SrcWksht = ThisWorkbook.Worksheets("Sheet1")

WkshtMon = SrcWksht.Name & " - Monthly "
Debug.Print WkshtMon
End Sub


--


Gary


"davegb" wrote in message
oups.com...
What's wrong with this:

Dim SrcWksht As Worksheet
Dim WkshtMon As Worksheet

Set SrcWksht = ThisWorkbook("Sheet1")

wkshtmon.Name=srcwksht.name&" - Monthly"<---Compile error - expected
end of statement

Thanks.

Can't concatenate
 

Tom Ogilvy wrote:
Your code says

set the name of some unknown worksheet to have the name of "Sheet1" & " -
Monthly"

Some way you have to establish which worksheet you want to rename.

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ups.com...

Tom Ogilvy wrote:
Sure,
you haven't set MonWksht to anything.

So you have an object variable undefined/Not Set.

--
Regards,
Tom Ogilvy

Thanks, Tom. So I can't determine a worksheet by setting it's name
based on another worksheet? Would it work if I set Monwksht to some
other sheet, like Sheet1, then changed it to SrcWksht.Name + "Monthly"?
I don't want to have to input the second sheet name directly. I want it
to be the source sheet name + "monthly". Is there a way to do this?


"davegb" wrote in message
oups.com...
I fixed it, but when I run it, I get an "Object variable or with block
variable not set" error.

Sub CountMonth()
Dim CountArray(1 To 12, 1 To 7)
Dim SrcWksht As Worksheet
'Dim SrcWkshtName As String
Dim MonWksht As Worksheet
Dim strCntctDate As String
Dim strCntctMo As String


Set SrcWksht = ThisWorkbook.Sheets("Sheet1")

MonWksht.Name = SrcWksht.Name & " - Monthly"<---error

Any suggestions?



Maybe I didn't explain this clearly. I'm not trying to rename a sheet.
I'm trying to set the variable MonWksht = to an existing worksheet,
which uses the current worksheet's name plus "monthly". For example,
the workbook has a series of sheets named for people on staff. Barry,
Sam, Sue, etc. It also has a series of sheets named "Barry - Monthly",
Sam - Monthly", etc. The source sheet where the macro will get it's
data is in the "Barry" sheet. Then it will count certain codes by month
and put the totals in "Barry - Monthly". I want to set SrcWksht =
"Barry" and MonWksht = "Barry" plus "Monthly" so it knows which sheet
to put the data in. All the sheets already exist. Does that help?

Can't concatenate
 

this will do that

ThisWorkbook.Worksheets("Sheet1").Name = "Sheet1 " & "- Monthly "

--


Gary


"davegb" wrote in message
oups.com...

Tom Ogilvy wrote:
Your code says

set the name of some unknown worksheet to have the name of "Sheet1" &
" -
Monthly"

Some way you have to establish which worksheet you want to rename.

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ups.com...

Tom Ogilvy wrote:
Sure,
you haven't set MonWksht to anything.

So you have an object variable undefined/Not Set.

--
Regards,
Tom Ogilvy

Thanks, Tom. So I can't determine a worksheet by setting it's name
based on another worksheet? Would it work if I set Monwksht to some
other sheet, like Sheet1, then changed it to SrcWksht.Name + "Monthly"?
I don't want to have to input the second sheet name directly. I want it
to be the source sheet name + "monthly". Is there a way to do this?


"davegb" wrote in message
oups.com...
I fixed it, but when I run it, I get an "Object variable or with
block
variable not set" error.

Sub CountMonth()
Dim CountArray(1 To 12, 1 To 7)
Dim SrcWksht As Worksheet
'Dim SrcWkshtName As String
Dim MonWksht As Worksheet
Dim strCntctDate As String
Dim strCntctMo As String


Set SrcWksht = ThisWorkbook.Sheets("Sheet1")

MonWksht.Name = SrcWksht.Name & " - Monthly"<---error

Any suggestions?



Maybe I didn't explain this clearly. I'm not trying to rename a sheet.
I'm trying to set the variable MonWksht = to an existing worksheet,
which uses the current worksheet's name plus "monthly". For example,
the workbook has a series of sheets named for people on staff. Barry,
Sam, Sue, etc. It also has a series of sheets named "Barry - Monthly",
Sam - Monthly", etc. The source sheet where the macro will get it's
data is in the "Barry" sheet. Then it will count certain codes by month
and put the totals in "Barry - Monthly". I want to set SrcWksht =
"Barry" and MonWksht = "Barry" plus "Monthly" so it knows which sheet
to put the data in. All the sheets already exist. Does that help?

Can't concatenate
 

How about:
dim monWksht as worksheet
....
set monwksht = nothing
on error resume next
set monwksht = worksheets(SrcWksht.Name & " - Monthly")
on error goto 0
if monwksht is nothing then
'nope--no sheet named that
else
'it worked!
end if





davegb wrote:
<<snipped

Maybe I didn't explain this clearly. I'm not trying to rename a sheet.
I'm trying to set the variable MonWksht = to an existing worksheet,
which uses the current worksheet's name plus "monthly". For example,
the workbook has a series of sheets named for people on staff. Barry,
Sam, Sue, etc. It also has a series of sheets named "Barry - Monthly",
Sam - Monthly", etc. The source sheet where the macro will get it's
data is in the "Barry" sheet. Then it will count certain codes by month
and put the totals in "Barry - Monthly". I want to set SrcWksht =
"Barry" and MonWksht = "Barry" plus "Monthly" so it knows which sheet
to put the data in. All the sheets already exist. Does that help?

--

Dave Peterson

Can't concatenate
 

It is nice to know what you are trying to do, because you code is trying to
do what I described.

If you want monWksht to contain a string, then you wouldn't dimension it as
a worksheet and it wouldn't have a name attribute

Sub CountMonth()
Dim CountArray(1 To 12, 1 To 7)
Dim SrcWksht As Worksheet
'Dim SrcWkshtName As String
Dim MonWksht As String
Dim strCntctDate As String
Dim strCntctMo As String


Set SrcWksht = ThisWorkbook.Sheets("Sheet1")

MonWksht= SrcWksht.Name & " - Monthly

' then you could do

Worksheets(MonWksht).Range("A1") = some value

--
Regards,
Tom Ogilvy



"davegb" wrote in message
oups.com...

Tom Ogilvy wrote:
Your code says

set the name of some unknown worksheet to have the name of "Sheet1" &
" -
Monthly"

Some way you have to establish which worksheet you want to rename.

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ups.com...

Tom Ogilvy wrote:
Sure,
you haven't set MonWksht to anything.

So you have an object variable undefined/Not Set.

--
Regards,
Tom Ogilvy

Thanks, Tom. So I can't determine a worksheet by setting it's name
based on another worksheet? Would it work if I set Monwksht to some
other sheet, like Sheet1, then changed it to SrcWksht.Name +
"Monthly"?
I don't want to have to input the second sheet name directly. I want
it
to be the source sheet name + "monthly". Is there a way to do this?


"davegb" wrote in message
oups.com...
I fixed it, but when I run it, I get an "Object variable or with
block
variable not set" error.

Sub CountMonth()
Dim CountArray(1 To 12, 1 To 7)
Dim SrcWksht As Worksheet
'Dim SrcWkshtName As String
Dim MonWksht As Worksheet
Dim strCntctDate As String
Dim strCntctMo As String


Set SrcWksht = ThisWorkbook.Sheets("Sheet1")

MonWksht.Name = SrcWksht.Name & " - Monthly"<---error

Any suggestions?



Maybe I didn't explain this clearly. I'm not trying to rename a sheet.
I'm trying to set the variable MonWksht = to an existing worksheet,
which uses the current worksheet's name plus "monthly". For example,
the workbook has a series of sheets named for people on staff. Barry,
Sam, Sue, etc. It also has a series of sheets named "Barry - Monthly",
Sam - Monthly", etc. The source sheet where the macro will get it's
data is in the "Barry" sheet. Then it will count certain codes by month
and put the totals in "Barry - Monthly". I want to set SrcWksht =
"Barry" and MonWksht = "Barry" plus "Monthly" so it knows which sheet
to put the data in. All the sheets already exist. Does that help?