Variable cell in vba code

  • Thread starter Thread starter Mikeice
  • Start date Start date
M

Mikeice

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
 
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
 
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.
 
Back
Top