I'm adding this line of code to save the current sheet name to retrieve
later. When I enter it, I get an "Compile error, expected list
separator or )" error and it highlights the "s4". I have nearly the
exact same line of code elsewhere in the program and it works fine,
just has a different sheet and cell location.

sheets ("County Records).Range("s4")= ActiveSheet.Name

Any ideas why this is happening here? I'm just trying to save the
sheetname so I can put in a button/macro to return to the source sheet
when I'm done with this one.
Thanks for the help.

sheets ("County Records).Range("s4")= ActiveSheet.Name
sheets("County Records").Range("s4")= ActiveSheet.Name


--
Don Guillett
SalesAid Software

"davegb" wrote in message
oups.com...
I'm adding this line of code to save the current sheet name to retrieve
later. When I enter it, I get an "Compile error, expected list
separator or )" error and it highlights the "s4". I have nearly the
exact same line of code elsewhere in the program and it works fine,
just has a different sheet and cell location.


Any ideas why this is happening here? I'm just trying to save the
sheetname so I can put in a button/macro to return to the source sheet
when I'm done with this one.
Thanks for the help.

Thanks, Don. I caught the missing quote just after I sent it. But it
still won't store the sheet name in the desired cell. Any ideas?

you usually don't have to do this but try:
sheets ("County Records").Range("s4").value = ActiveSheet.Name


"davegb" wrote:

Thanks, Don. I caught the missing quote just after I sent it. But it
still won't store the sheet name in the desired cell. Any ideas?

What's the sheet name?

Maybe:

sheets("County Records").Range("s4").value = "'" & ActiveSheet.Name

(to force it to be text. I added .value, 'cause I like it!)

davegb wrote:

I'm adding this line of code to save the current sheet name to retrieve
later. When I enter it, I get an "Compile error, expected list
separator or )" error and it highlights the "s4". I have nearly the
exact same line of code elsewhere in the program and it works fine,
just has a different sheet and cell location.

sheets ("County Records).Range("s4")= ActiveSheet.Name

Any ideas why this is happening here? I'm just trying to save the
sheetname so I can put in a button/macro to return to the source sheet
when I'm done with this one.
Thanks for the help.

--

Dave Peterson

Dave Peterson wrote:
What's the sheet name?

Maybe:

sheets("County Records").Range("s4").value = "'" & ActiveSheet.Name

(to force it to be text. I added .value, 'cause I like it!)

davegb wrote:

I'm adding this line of code to save the current sheet name to
retrieve
later. When I enter it, I get an "Compile error, expected list
separator or )" error and it highlights the "s4". I have nearly the
exact same line of code elsewhere in the program and it works fine,
just has a different sheet and cell location.

sheets ("County Records).Range("s4")= ActiveSheet.Name

Any ideas why this is happening here? I'm just trying to save the
sheetname so I can put in a button/macro to return to the source
sheet
when I'm done with this one.
Thanks for the help.

--

Dave Peterson

Thanks for the reply, Dave.
I tried adding the ".value" after I posted here. Didn't help either!

Did you try adding the single quote? (I didn't expect the .value to be the
solution--I just like to explicitly use properties.)

(and you didn't answer my question about the sheetname.)

davegb wrote:

Dave Peterson wrote:
What's the sheet name?

Maybe:

sheets("County Records").Range("s4").value = "'" & ActiveSheet.Name

(to force it to be text. I added .value, 'cause I like it!)

davegb wrote:

I'm adding this line of code to save the current sheet name to
retrieve
later. When I enter it, I get an "Compile error, expected list
separator or )" error and it highlights the "s4". I have nearly the
exact same line of code elsewhere in the program and it works fine,
just has a different sheet and cell location.

sheets ("County Records).Range("s4")= ActiveSheet.Name

Any ideas why this is happening here? I'm just trying to save the
sheetname so I can put in a button/macro to return to the source
sheet
when I'm done with this one.
Thanks for the help.

--

Dave Peterson

Thanks for the reply, Dave.
I tried adding the ".value" after I posted here. Didn't help either!

--

Dave Peterson

Dave,
The single quote didn't seem to have any effect. The sheet name is
"County Records". Any other ideas?

Just realized maybe you meant the other sheet name, the one I'm
storing. In this test case, it is "Recurrence".

I figured it out! I was pasting the data from an advanced filter into
the "County Records" sheet after I put the name of the source sheet in
S4. Even though the data was not going into the cell, it was erasing
the entire sheet before the data was pasted, and eraxing the sheet name
in S4 with it. I saved the original sheet name as a variable, then
pasted it into S4 after the clearing and pasting was done.
Thanks for the help!