What I am trying to do is based on a cell in the MENU sheet B25
I want the blue hightlight in the cell to become the same.

Eg. IF B
B25 is July then the worksheet name will become July.

Is that possible?

Please help




Private Sub CommandButton7_Click()

Dim wbk As Workbook
Dim wks As Worksheet
Dim SrcBook As Workbook

Set SrcBook = ActiveWorkbook


Set wbk = Workbooks.Open("K:\Data.xls")
Set wks = wbk.Sheets("Sheet1")


Application.ScreenUpdating = False

SrcBook.Sheets("Transfer").UsedRange.Copy Destination:= _
wks.Range("B2")

wbk.Close (False)

Application.ScreenUpdating = True

End Su

--
Mikeic
-----------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...fo&userid=2246
View this thread: http://www.excelforum.com/showthread.php?threadid=39151

Good morning Mikeice

Sorry, but I'm not quite sure what you're after here. Do you want to
rename the worksheet after the contents of a cell? If so, does this
piece of code help you at all:

a = Range("B25").Value
ActiveSheet.Name = a

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=391515

HI There

thanks for your response.

What I need is the workbook I am copy to has 12 sheet names.
Jan - Dec

So based on the cell I need the appropraite sheet to be selected before
copy and pasting the worksheet contents.


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=391515