Solved

Explicitly reference a sheet

Posted on 2013-12-03
5
142 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 48

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
excel file 5 44
Automatic snapshot of data in excel triggers on a date 3 21
Merging multiple rows to one 22 33
onOpen 14 38
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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 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

16 Experts available now in Live!

Get 1:1 Help Now