::Referencing Named Worksheet::

jekstein

New Member
Joined
Aug 31, 2006
Messages
18
Hi,

im trying to create some universal code i can use on other projects the sheets are named after people when i write my code using these names it works perfect but when i change it to reflect the sheet number it gives me an error.

WORKS:
Code:
Sub copyLink()
' copyLink Macro
' Macro recorded 8/31/2006 by

    Sheets("gowans").Range("A5:P31").Copy
    Sheets("Total Sheet").Range("A5:P31").Select
    ActiveSheet.Paste Link:=True
End Sub

DOESN'T WORK:
Code:
Sub copyLink()
' copyLink Macro
' Macro recorded 8/31/2006 by

    Sheets("sheet1").Range("A5:P31").Copy
    Sheets("sheet13").Range("A5:P31").Select
    ActiveSheet.Paste Link:=True
End Sub

am i referencing the sheet numbers wrong? Is it possible to write working code this way (referencing the sheet number instead of the name)

Thanks in advance
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
how is is not working? Do you have sheet13?

try this
Code:
Sheets("sheet1").Range("A5:P31").Copy Destination:=Sheets("Sheet3").Range("A5:P31")
 
Upvote 0
Are sheet1 and sheet13 the codenames of the sheets rather than the name on the worksheet tab?
 
Upvote 0
the names on the tabs are the crew names.. not the sheet numbers.. so yeah i guess they are 'code names'
 
Upvote 0
Well to use the codenames in VBA you use this syntax.
Code:
Sub copyLink()
' copyLink Macro
' Macro recorded 8/31/2006 by

    Sheet1.Range("A5:P31").Copy
    Sheet13.Range("A5:P31").Select
    ActiveSheet.Paste Link:=True
End Sub
There are also other ways to deal with the name of a worksheet changing. eg looping through worksheets like this.
Code:
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
         ' do something with the worksheet ws
         Msgbox ws.Name
    Next ws
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top