PDA

View Full Version : sheet code name not working



ragamuffin
11-25-2006, 02:42 PM
Hi,

I have a macro that references several sheets. I do not want to use the Sheet Tab Name or the Index Number to reference these sheets, as the titles of the sheets and thier positions in the workbook will change. I type the Code Name for the sheet (for example, Sheet3.Select) but it will not reference the sheet accordingly.

Can anyone help understand what I am doing wrong or how to make the code work?

Thanks.

Norie
11-25-2006, 04:05 PM
Can you post your code?

For one thing there is normally no need to use Select.

JeffT
11-25-2006, 04:47 PM
have you tried

Sheets(3).Select

I've just copied that from code that works for me. I think you need the ()

Jeff

mdmackillop
11-25-2006, 05:55 PM
Hi Ragamuffin,
Your code should work, as in

Sub test()
Sheet2.Range("B2") = 5
End Sub

johnske
11-25-2006, 07:08 PM
Should work, it's very basic. Are you sure you're referencing the correct sheet? Is there a sheet with the code name Sheet3? See here (http://www.vbaexpress.com/forum/showthread.php?t=9771)

ragamuffin
11-27-2006, 01:00 PM
From my understanding of the article "Referencing Work Sheets," using "Sheets(3)" is using the index name, not the code name. I really want to use the code name to keep the code actively working without errors when the given name and index number change (which they will, as the sheets are named after a store roster - so everytime a person is added or taken away the sheet will be renamed and most likely repositioned). I noticed the example in the article for using the code name. It reads "Sheet1.Activate ." I am currently using the index name for my code ("Sheets(3).Select") and it works fine. But when I adjust the code to read "Sheet3.Select" it does not work.... So I am stuck and I am not sure what I am doing wrong.

Thanks again to everyone for all of their help - I wasn't even using the index numbers before I began this post, and that has been a great tool to add to my VBA bag-o-goodies :thumb I just need to get the code name in there and all will be right with the world!

ragamuffin
11-27-2006, 01:02 PM
Oh, here is a sample of my code just to maybe make things a bit easier to understand:

Sheets(3).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=24

I know there is a cleaner way to do this, but I just trying to get the code name worked out for now.

Thanks!

mdmackillop
11-27-2006, 01:08 PM
Using code names

Sub Macro1()
Sheet3.Range("A2:G47").Copy Sheet2.Range("A1")
End Sub

ragamuffin
11-27-2006, 02:45 PM
I get Runtime error 404...?

mdmackillop
11-27-2006, 02:50 PM
Have you checked your code names match the code?
try

Sub cNames()
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).CodeName
Next
End Sub

JeffT
11-28-2006, 11:21 AM
Ragamuffin

I tried

Sheet1.Range("B1").Copy Sheet2.Range("B38")

Which is as mdmackillop's code & it works (so elegant saves all the .select etc)

Are you sure you are referenceing the correct sheet? Try naming them temporarily maybe and seeing which sheet is which. If they've already been moved perhaps you're referencing to the wrong sheet. In vb the sheet1 sheet2 etc stay in that order nomatter where they are in Excel.

Jeff

ragamuffin
11-28-2006, 11:25 AM
Well, I guess my "newbiness" is showing through, because I cannot get the code to work. I have tried copying what you posted into VBA and typing a different shortcut key - no luck. I tried going through the Tools menu in Excel to select the macro from the available list and running it that way - still nothing (it wasn't even on the list). Sorry, I know this is a rediculous question - but, how do I try using your code. Most likely the code you posted earlier will work great [Sheet3.Range("A2:G47").Copy Sheet2.Range("A1")], but I am just doing it all wrong. I really appreciate all of you help and patience! Thank you!

mdmackillop
11-28-2006, 11:40 AM
Hi Ragamuffin
I think it's time to post your workbook so we can try the code properly with whatever setup you have.
Regards
MD

mdmackillop
11-28-2006, 11:44 AM
Hi Jeff,
The code uses the sheet's code name which can be seen in the VBE, and not the name on the visible tab.

Hellboy
11-28-2006, 11:52 AM
Using code names

Sub Macro1()
Sheet3.Range("A2:G47").Copy Sheet2.Range("A1")
End Sub



Hi

Sub Macro1()
Sheets(3).Range("A2:G47").Copy destination:=Workbooks(?)Sheet(2).Range("A1")
End Sub

or

Sub Macro1()
With WorkSheets(3)
.Range(.cells(2,1),.cells(47,7)).Copy Destination:=Workbooks(?)Sheet(2).Range("A1")
End with
End Sub

Philippe

Hellboy
11-28-2006, 11:56 AM
Oh, here is a sample of my code just to maybe make things a bit easier to understand:

Sheets(3).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=24

I know there is a cleaner way to do this, but I just trying to get the code name worked out for now.

Thanks!

Simplefy:


Sheets(3).Activate
Activesheet.Range("A2:G47").copy Destination:= Workbooks(?).Sheets(2).Range("G47")

But something looks funny in the sequence of your code.

Phil

mdmackillop
11-28-2006, 12:20 PM
Hi Phil
Welcome to VBAX
Refer to post #14 regarding the disctinction between sheet names and sheet codenames. Sheets(3) uses the sheet index, which may or may not refer to Sheet3 of either type.

JeffT
11-28-2006, 12:31 PM
mdm

Yes I realised it was the sheet code name (another new one for me), but if you move the sheets then rename them ragamuffin may still have been thinking that his third sheet was Sheet3 (though I know it also has the name after it which I had forgotten), and may have been trying to go to the third sheet which was no longer Sheet3. Perhaps Sheet3 or Sheet2 code names no longer exist having been deleted at some point?

I've just used a blank workbook and have

Sheet1(Sheet2)
Sheet4(Sheet1)
Sheet5(Sheet3)

in VBE.

Using your code I get runtime error 424 cause I've deleted Sheet2 & Sheet3 (Code names). It is possible to get confused about which name is which if you've been messing round with names and moving sheets. Still Ragamuffin got error 404 so probably not this.
Jeff

Hellboy
11-28-2006, 01:01 PM
Hi Phil
Welcome to VBAX
Refer to post #14 regarding the disctinction between sheet names and sheet codenames. Sheets(3) uses the sheet index, which may or may not refer to Sheet3 of either type.

Hi

I personnaly know the diffrence, I was just using the exemple as the reference.

Phil

ragamuffin
11-28-2006, 01:02 PM
WOW! Thanks for all the feedback and help everyone! Here is my edited workbook, as the real thing is too large to attach. The code is as follows:

Sub ESN_Summary()
'
' ESN_Summary Macro
' Macro recorded 10/30/2006 by pos1
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ActiveSheet.Unprotect

Sheets(3).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=24

Sheets(4).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
Range("B50").Select
ActiveSheet.Paste

Sheets(5).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveWindow.SmallScroll Down:=42
Range("B96").Select
ActiveSheet.Paste

Sheets(6).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveWindow.SmallScroll Down:=45
Range("B142").Select
ActiveSheet.Paste

Sheets(7).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveWindow.SmallScroll Down:=39
Range("B188").Select
ActiveSheet.Paste

Sheets(8).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveWindow.SmallScroll Down:=51
Range("B234").Select
ActiveSheet.Paste

Sheets(9).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveWindow.SmallScroll Down:=54
Range("B280").Select
ActiveSheet.Paste

ActiveWindow.ScrollRow = 1

Sheets.Select


Sheets(3).Activate

Range("A2").Select

Sheets(2).Select

ActiveSheet.Unprotect
Selection.AutoFilter Field:=1, Criteria1:="<>"
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
End Sub

I used the macro recorder to get the code and then substituted the Sheet Tab Names for the Index Names to make it a bit closer to what I need. The real workbook has twice as many tabs to be referenced for summary purposes, so I know that if I can just get one sheet to reference correctly, then I should just be able to reproduce the code for all of the others. Please remember that I am very new to all of this - in fact I really do not understand all of this code yet - so for any new code suggestions I would love to know exactly what the code is doing and how to run it/make it work.

I am, again, very sorry for my lack of knowledge. I am asking for a VBA Book for Dummies for Christmas...

Thanks again to all!

ragamuffin

Hellboy
11-28-2006, 01:16 PM
WOW! Thanks for all the feedback and help everyone! Here is my edited workbook, as the real thing is too large to attach. The code is as follows:

Sub ESN_Summary()
'
' ESN_Summary Macro
' Macro recorded 10/30/2006 by pos1
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ActiveSheet.Unprotect

Sheets(3).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=24

Sheets(4).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
Range("B50").Select
ActiveSheet.Paste

Sheets(5).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveWindow.SmallScroll Down:=42
Range("B96").Select
ActiveSheet.Paste

Sheets(6).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveWindow.SmallScroll Down:=45
Range("B142").Select
ActiveSheet.Paste

Sheets(7).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveWindow.SmallScroll Down:=39
Range("B188").Select
ActiveSheet.Paste

Sheets(8).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveWindow.SmallScroll Down:=51
Range("B234").Select
ActiveSheet.Paste

Sheets(9).Select
Range("A2:G47").Select
Range("G47").Activate
Selection.Copy
Sheets(2).Select
ActiveWindow.SmallScroll Down:=54
Range("B280").Select
ActiveSheet.Paste

ActiveWindow.ScrollRow = 1

Sheets.Select


Sheets(3).Activate

Range("A2").Select

Sheets(2).Select

ActiveSheet.Unprotect
Selection.AutoFilter Field:=1, Criteria1:="<>"
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
End Sub

I used the macro recorder to get the code and then substituted the Sheet Tab Names for the Index Names to make it a bit closer to what I need. The real workbook has twice as many tabs to be referenced for summary purposes, so I know that if I can just get one sheet to reference correctly, then I should just be able to reproduce the code for all of the others. Please remember that I am very new to all of this - in fact I really do not understand all of this code yet - so for any new code suggestions I would love to know exactly what the code is doing and how to run it/make it work.

I am, again, very sorry for my lack of knowledge. I am asking for a VBA Book for Dummies for Christmas...

Thanks again to all!

ragamuffin


Sub ESN_Summary()
Dim bytCount as Byte

For bytCount = 3 to 9
Sheets(bytCount).Select
Range("A2:G47").copy _
Destination:= Thisworkbook.Sheets(2).cells(Thisworkbook.Sheets(2).cells(65000,7).end(xlup ).row +2,2)
next bytCount
With Sheets(2)
.Select
.Range("A2").AutoFilter Field:=1, Criteria1:="<>" ' Not sure here
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
.EnableSelection = xlNoSelection
End with
End Sub

ragamuffin
11-28-2006, 01:37 PM
Thanks Hellboy! I'd like to try the code, but outside of recording a macro and using the shortcut keys I define, I do not know how to add this into my worksheet and run it. Please let me know how.

Thanks.

Hellboy
11-28-2006, 01:48 PM
Thanks Hellboy! I'd like to try the code, but outside of recording a macro and using the shortcut keys I define, I do not know how to add this into my worksheet and run it. Please let me know how.

Thanks.

Harder for me to explain ! Working in french here.

Let see( anyone can fill in please !) On the "View" menu(I think), On the tool bar option, there is a tool bar called "Form" (I think). On that tool bar there is a button. Put this Button on the Sheets(2). Then right click on it. There is an option that ask you to assign a macro to it. assign the one you recorded. Then press alt+F11 go to the folder Module. Double on that then access the macro. Then replace the code that is inside with the one I suggested.

Cross my fingers.

Phil

mdmackillop
11-28-2006, 02:11 PM
I do not want to use the Sheet Tab Name or the Index Number to reference these sheets, as the titles of the sheets and their positions in the workbook will change
I didn't realise that we were dealing with a question I thought was resolved earlier. No problem in continuing in a new thread with a new issue, but a reference back would help.
As far as I can see, you don't need to use code names, which would involve possible rewriting of code each time the workbook changed. You've added a Totals sheet, since I last saw this, which is what has upset my previous code.
I suggest that you loop through all sheets, but do nothing when the sheet name is on an excluded list (see the Select statement). This way, order etc. doesn't matter. You could achieve a similar result by say putting an "x" in a specific cell, which would exclude a sheet from processing: whaterver is easiest.
Anyway, here's my revised code and revised workbook example.

Sub ESN_Summary()
'
' ESN_Summary Macro
' Macro by MD
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Dim Tgt As Range, Rng As Range, sh As Worksheet
Sheets("ESN Summary").Unprotect
For Each sh In Worksheets
Select Case sh.Name
Case "Totals", "ESN Summary"
'do nothing
Case Else
With Sheets("ESN Summary")
Set Tgt = .Cells(Rows.Count, 2).End(xlUp).Offset(2)
sh.Range("A2:G47").Copy Tgt
Set Rng = Tgt.Resize(46).SpecialCells(xlCellTypeConstants)
Rng.Offset(, -1).Formula = sh.Name
End With
End Select
Next sh
Sheets("ESN Summary").Activate
Range("A3:H3000").AutoFilter Field:=1, Criteria1:="<>"
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
End Sub

ragamuffin
11-28-2006, 02:21 PM
OK! I got the code to work! Thank you for the help! I still need to tweek it a bit, though. How does this code bypass the Code Name for the sheets and if/when the sheets change in name or order, will this code still work?

Also, how does the copy function work in your version? Mine highlited A2:G47 and copied, then pasted on the Summary sheet (with every paste occuring where the last one ended, and then the sheet was just filtered to NonBlanks). Your's seems to get almost all of the information (4 items are being left out) and paste it within the first 30 rows. That's awesome! How does that work and how can the code be made to copy all of the information?

mdmackillop
11-28-2006, 03:04 PM
Can you clarify the missing items?

With Sheets("ESN Summary")
'Find the cell two rows below the last used cell in column B
Set Tgt = .Cells(Rows.Count, 2).End(xlUp).Offset(2)
'Copy the specified range from each sheet in turn and paste it into the Target cell (Tgt)
sh.Range("A2:G47").Copy Tgt
'Find all the new pasted cells in column B containing a value
Set Rng = Tgt.Resize(46).SpecialCells(xlCellTypeConstants)
'Enter the sheet name in corresponding cells in column A
Rng.Offset(, -1).Formula = sh.Name
End With

mdmackillop
11-28-2006, 03:13 PM
The only sheet names that matter are ESN Summary, Totals, and any others you don't want the data copied from. These will be added to the first bit of the Select Case code. Rep names will then be processed regardless of order or name.


'Look at each sheet in turn
For Each sh In Worksheets
'Get the sheet name
Select Case sh.Name
'If it's in this list
Case "Totals", "ESN Summary"
'then don't do anything
'do nothing
'Otherwise do what comes next
Case Else

ragamuffin
12-02-2006, 01:57 PM
Thank you soooo much for explaining all of that to me! That is invaluable help! I am running into an error now that I try your macro (I got Helloboy's macro to work, but have not been able to get yours). I get the following error: Run-time error '1004' NO cells were found. The debugger points to the following line of code: Set Rng = Tgt.Resize(46).SpecialCells(xlCellTypeConstants) Could you help me troubleshoot this?

Thanks again!