VBA refers to tab name

[xrull]

I've got a problem. The tabname of the sheet keeps changing (or people
keep changing it) and I've got to keep changing the VBA code.
Is it possible for the VBA code to pick up the tab name in the code
instead of the direct reference?
I used this code:
Run_Me_Now()
Sheets("ABC").Select
Columns("A:I").Select
Selection.ClearContents
Sheets("XYZ").Select
Columns("A:I").Select
Selection.Copy
Sheets("ABC").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

But when I changed it to this code:

Worksheets(2).Select
Columns("A:I").Select
Selection.ClearContents
Worksheets(1).Range("A:I").Select
Columns("A:I").Select
Selection.Copy
Worksheets(2).Select
Range("A1").Select
ActiveSheet.Paste
End Sub

It fails.
I've searched the forum, but I'm unable to find an answer.
Please help
Joni

 

[keiji kounoike]

Instead of using Worksheets collection's Property, Use Worksheet's
Object name. Like Sheet2.select, if your Worksheet(2)'s object name is
Sheet2.

keiji

 

[xrull]

Instead of using Worksheets collection's Property, Use Worksheet's
Object name. Like Sheet2.select, if your Worksheet(2)'s object name is
Sheet2.

keiji

I tried it in the code, but it didn't work.
Thanks,
Xrull

 

[keiji kounoike]

I tried it in the code, but it didn't work.
Thanks,
Xrull

Sorry for the inconvenience.
But What does your code look like? and How did you check your
worksheet's object name? Are you sure your worksheet's object name
surely exist?

keiji

 

[muddan madhu]

try this !

Sub Run_Me_Now()
x = ActiveSheet.Name
y = ActiveSheet.Next.Name
Sheets(x).Select
Columns("A:I").Select
Selection.ClearContents
Sheets(y).Select
Columns("A:I").Select
Selection.Copy
Sheets(x).Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

 

[NickViv]

It sounds to me that the root cause of the problem is allowing them
too much control over your sheet... from a 5000 ft level, this is a
bad thing to do.

suggest 2 things:

1. Make the sheet "Read Only" so that users cannot change it.
- to do that, right click on the file in Explorer, and go to
properties => attributes
- when you want to modify it yourself, uncheck that box, and proceed
as normal.

2. IF that cannot work due to your users needing to save data on the
sheet, you can protect the workbook structure
- this will disable them from adding sheets and changing sheet names,
but will allow them to change data.

As a side note, you can perform a copy operation with one line of
code:
Sheets(1).Range("A1:B5").Copy (Sheets(2).Range("A1:B5")) ' for
example
- the first range is the source, and the 2nd is the destination

Rgds

Nick


http://www.excelexperts.com/
Free Excel based systems for the world

 

[SmartbizAustralia]

What on earth are you trying to do?

You can loop through each worksheet in code...with a for each
worksheet in thisworkbook.....

But how are you going to know it's the correct worksheet?

Also, if the users keep changing the worksheet names, then you'll need
to get them to use a naming system or at least have one cell distinct
enough to know that it is the one..

Regards,
Tom Bizannes
Excel and Access Specialist
Sydney, Australia

 

[Don Guillett]

sub trythisOneLinerNOselections()
sheets(1).Columns("A:I").Copy sheets(2).Range("A1")
end sub

 

[Dave Peterson]

It should work.

Did you use the codename for the worksheets?

You can find the codename by:
Opening the workbook in excel
Going into the VBE
Hitting ctrl-r to see the project explorer
selecting your project
Expand the branches to see the sheet levels

You'll see things like:
Sheet1 (NameYouSeeOnTheTabInExcel)

Sheet1 is the codename for this sheet.

You can change the codename to be significant by hitting the F4 key when the
Sheet is selected in the project explorer.

The (Name) property is the codename. The Name property is what you see in
excel.

Then instead of using:

worksheets("yoursheetnamehere").range("a1").value = "xxx"
you can refer to by its codename:
Sheet1.range("a1").value = "xx"

or if you used a nice code name:
InputSheet.range("a1").value = "xx"

The codename is much more difficult for the typical user to change. Most won't
know that it exists and the others shouldn't care.

======
If you want to stop the users from changing the layout of your workbook--no
renaming sheets, no adding/deleting/moving/hiding/unhiding sheets, you can
protect the workbook.

In xl2003 menus:
tools|protection|protect workbook
Check Structure

 

[Gord Dibben]

To help you with the codenames run this macro on an inserted sheet.

Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet
For I = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(I)
.Cells(I, 1).Value = ws.Name
.Cells(i, 2).Value = ws.CodeName
End With
Next I
End Sub


Gord Dibben MS Excel MVP

 

[xrull]

It should work.

Did you use the codename for the worksheets?

You can find the codename by:
Opening the workbook in excel
Going into the VBE
Hitting ctrl-r to see the project explorer
selecting your project
Expand the branches to see the sheet levels

You'll see things like:
Sheet1 (NameYouSeeOnTheTabInExcel)

Sheet1 is the codename for this sheet.

You can change the codename to be significant by hitting the F4 key when the
Sheet is selected in the project explorer.

The (Name) property is the codename.  The Name property is what you seein
excel.

Then instead of using:

worksheets("yoursheetnamehere").range("a1").value = "xxx"
you can refer to by its codename:
Sheet1.range("a1").value = "xx"

or if you used a nice code name:
InputSheet.range("a1").value = "xx"

The codename is much more difficult for the typical user to change.  Most won't
know that it exists and the others shouldn't care.

======
If you want to stop the users from changing the layout of your workbook--no
renaming sheets, no adding/deleting/moving/hiding/unhiding sheets, you can
protect the workbook.

In xl2003 menus:
tools|protection|protect workbook
Check Structure

I used the code name, but the draw back is, if you move the position
of the a sheet, the code sometimes fails, but I guess the code will
work. I'm wondering if I could use the cell name to draw the name in
the code.
Thanks,
Xrull

 

[Dave Peterson]

Moving the sheet doesn't affect the codename.

Moving the sheets will affect code like:
worksheets(1)....
sheets(2)....