Creating Variable for a Sheet

I have to copy data from one sheet to another. I am sending this program for
others to use and I don't know what they will name the sheet. I have a macro
that uses the ActiveSheet function right now, but I was hoping to get
something that is less sensitive and set up a variable for the entire sheet.
Thanks,
Kyle Kyburz

Hi Kyle

If it's not the active sheet, how would the code recognize it ? Is there
anything unique in it ? And where is the macro, how is it started ?

Best wishes Harald

"kyle" <[email protected]> skrev i melding
news:[email protected]...
> I have to copy data from one sheet to another. I am sending this program
for
> others to use and I don't know what they will name the sheet. I have a
macro
> that uses the ActiveSheet function right now, but I was hoping to get
> something that is less sensitive and set up a variable for the entire
sheet.
> Thanks,
> Kyle Kyburz

I have a sheet called "Middle Sheet". In cell C1 I have the name of a
person. All other sheet's names in the workbook are a person's name. What I
would like is to take the name in C1 and put it into some sort of variable.
Then take that variable and open the sheet. So I can copy data into the
sheet.
thanks,
kyle

"Harald Staff" wrote:

> Hi Kyle
>
> If it's not the active sheet, how would the code recognize it ? Is there
> anything unique in it ? And where is the macro, how is it started ?
>
> Best wishes Harald
>
> "kyle" <[email protected]> skrev i melding
> news:[email protected]...
> > I have to copy data from one sheet to another. I am sending this program
> for
> > others to use and I don't know what they will name the sheet. I have a
> macro
> > that uses the ActiveSheet function right now, but I was hoping to get
> > something that is less sensitive and set up a variable for the entire
> sheet.
> > Thanks,
> > Kyle Kyburz
>
>
>

also, since I am sending this program out to be used by others I don't know
the names of the sheets. That is why I need it in variable form.
thanks,
kyle

"kyle" wrote:

> I have a sheet called "Middle Sheet". In cell C1 I have the name of a
> person. All other sheet's names in the workbook are a person's name. What I
> would like is to take the name in C1 and put it into some sort of variable.
> Then take that variable and open the sheet. So I can copy data into the
> sheet.
> thanks,
> kyle
>
> "Harald Staff" wrote:
>
> > Hi Kyle
> >
> > If it's not the active sheet, how would the code recognize it ? Is there
> > anything unique in it ? And where is the macro, how is it started ?
> >
> > Best wishes Harald
> >
> > "kyle" <[email protected]> skrev i melding
> > news:[email protected]...
> > > I have to copy data from one sheet to another. I am sending this program
> > for
> > > others to use and I don't know what they will name the sheet. I have a
> > macro
> > > that uses the ActiveSheet function right now, but I was hoping to get
> > > something that is less sensitive and set up a variable for the entire
> > sheet.
> > > Thanks,
> > > Kyle Kyburz
> >
> >
> >

Sheets have a TAB name and a Code name.
The code name can only be changed in the VBE.

So you should use that name to reference your sheet.

That would work except the people using the sheet need to be able to add
sheets, so I would not be able to reference. Could I create a prompt and
have the user type in the head name?

"Kaak" wrote:

>
> Sheets have a TAB name and a Code name.
> The code name can only be changed in the VBE.
>
> So you should use that name to reference your sheet.
>
>
> --
> Kaak
> ------------------------------------------------------------------------
> Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513
> View this thread: http://www.excelforum.com/showthread...hreadid=557001
>
>

have you tried adding

Dim ws as worksheet
ws=sheets("Middle Sheet").Range("C1").value
ws.select

"kyle" wrote:

> That would work except the people using the sheet need to be able to add
> sheets, so I would not be able to reference. Could I create a prompt and
> have the user type in the head name?
>
> "Kaak" wrote:
>
> >
> > Sheets have a TAB name and a Code name.
> > The code name can only be changed in the VBE.
> >
> > So you should use that name to reference your sheet.
> >
> >
> > --
> > Kaak
> > ------------------------------------------------------------------------
> > Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513
> > View this thread: http://www.excelforum.com/showthread...hreadid=557001
> >
> >

have you tried adding

Dim ws as worksheet
ws=sheets("Middle Sheet").Range("C1").value
ws.select

"kyle" wrote:

> That would work except the people using the sheet need to be able to add
> sheets, so I would not be able to reference. Could I create a prompt and
> have the user type in the head name?
>
> "Kaak" wrote:
>
> >
> > Sheets have a TAB name and a Code name.
> > The code name can only be changed in the VBE.
> >
> > So you should use that name to reference your sheet.
> >
> >
> > --
> > Kaak
> > ------------------------------------------------------------------------
> > Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513
> > View this thread: http://www.excelforum.com/showthread...hreadid=557001
> >
> >