Solved

Explicitly reference a sheet

Posted on 2013-12-03
5
157 Views
Last Modified: 2013-12-03
Hi,

Im going back and forward to a worksheet with a very long name, so am trying to call it something short to make my code a bit shorter and neater

The below is not working for me, can someone explain how i can just call my activesheet "LOB" and then refer to that when im going back and forth

Thanks

....................................



Sub CopyFormats()

Dim SheetName As String
SheetName = "LOB"





    Range("G4:G32").Select
    Selection.Copy
    Sheets("RBWM").Select
    Range("H4").Select
     Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
   Sheets("LOB").Select
0
Comment
Question by:Seamus2626
  • 2
  • 2
5 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39692193
Hi
pls try

Set LOB_Worksheet = Sheets("the long name of the Sheet")
' instead of Sheets("LOB").Select 
' use
LOB_Worksheet.Select

Open in new window

Regards
0
 

Author Closing Comment

by:Seamus2626
ID: 39692196
Perfect, thanks
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39692465
I know it is closed but why don't you just rename the sheet in the VBA property window?  It's much simpler and you do not need any code. Moreover if at one point you want to change the sheet tab name this will not affect your code
0
 

Author Comment

by:Seamus2626
ID: 39693099
Thanks for the update yuppydu!
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39693142
My pleasure Seamus, if it is not clear how to do that write me a line... and BTW try to avoid the .Select command, there are very few occasions where it is necessary
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now