I have been using the syntax "Sheets(1)" and "Sheets(2)" just in case their worksheet names changes.
The code snippet below copies values from sheet 1 to sheet 2.
I noticed in Excel 2016 that when I add a new sheet, it copies values to this new sheet, not the original "Sheets(2)".
How can it work without specifying the name of the sheet?
Please Login or Register to view this content.
When you refer to sheets by number, that number is their order in the workbook.
What was Sheets(2) can become Sheets(3) if the user moves them.
It sounds like what you are looking for is to refer to sheets by their code name. This link explains the different ways to refer to a sheet
https://www.mrexcel.com/forum/excel-...-etcetera.html
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Thank you.
OK, I'm running into problems ... have done research but ... nothing.
See the code below ... I'm trying to find the last row in Sheet2 (this is the VBA name Sheet2, not the tab name "Sheet2").
I now understand that Sheets(2) refers to the second sheet in the order laid out in the workbook. It may have nothing to do with VBA name Sheet2.
So if I insert another sheet, it will refer to the second in order, which may not be VBA Sheet2.
I realize I can refer to it as Sheets("Sheet2") but the tab name may change ... it has to be the VBA name Sheet2.
Obviously Sheets(Sheet2) doesn't work.
Please Login or Register to view this content.
When you add sheets just add them at the end:
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Sorry but I do have to rearrange them.
Well then you'll need to address them by name
Was there no other way?
Thanks anyway.
Not if you change the order
It’s just:
Please Login or Register to view this content.
Rory
No, this is isn't working for me.RowLast2 = Sheet2.UsedRange.SpecialCells(xlCellTypeLastCell).Row
What do you mean byIn Post#4 you say yourself that Sheet2 is the sheet's CodeName ( the one NOT between parenthesis)so why wouldn't that work ?this is isn't working for me.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
OK, now I know why.
I'm calling up the macro from an Add-In.
It appears there is no way around it, not that I can see???
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Sorry but I don't understand the work in post #14.
It sets the worksheet variable wks2 to the sheet in the active workbook whose codename is Sheet2.
OK thanks I may get this to work.
Great it works. Thanks.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks