Name a worksheet using a cell and VBA

Drmwvr7266

Board Regular
Joined
Apr 7, 2002
Messages
164
I am working on some code that will refer back to a previous worksheet. The problem is not knowing the worksheet's name until I am ready to run the code.

Sheet names start as, for example, 1809-1. Then, each month, as they are added, the name changes to 1809-2, 1809-3, etc.

I have a code that copies information from the previous month's sheet, paste it into the current month's, and rearranges the numbers to give a current balance.

Since I have most of the code working,here's what I am looking for:

Sheets(Range("F3")).select

The name of the sheet that the code has to select is in a range. However, my code hangs up here. The cell (F3) looks as such: 1809-1.
I tried it as "1809-1" but no luck yet. I have tried it with a word, with quotes, without. Running out of ideas, aside from creating a userform that would feed the code the necessary data.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Does this help?

<pre>
Sub tabname()
Dim ws As Worksheet

For Each ws In Worksheets
ws.Activate
ws.Name = Range("$D$5").Value
Next
End Sub


</pre>

will name every sheet in the book with the value in D5 on the sheet.

Paddy
 

Drmwvr7266

Board Regular
Joined
Apr 7, 2002
Messages
164
I just tested that before you posted! Thank you! The answer I needed was:

Sheets(Range("F3").value).select

Thankyou!
This message was edited by Drmwvr7266 on 2002-09-03 14:50
 

Forum statistics

Threads
1,144,329
Messages
5,723,734
Members
422,512
Latest member
MHau5

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
Top