PDA

View Full Version : Input box to select sheet then rename sheet



geebra
09-21-2012, 03:14 PM
Hi,

I have this code and it does a search of a closed workbook and copy a sheet and then pastes in another workbook.

But I want to rename the copied sheeet as "Whole" and I have tried many ways but none would work. Can anyone help?

Sub fetch()

' This macro will import a file into this workbook
Dim controlfile, pathname, tabname2, filename, tabname As Variant
Dim inputpathname, inputtabname, newtabname, wb As Variant
Dim inputfilename As String

inputpathname = "C:\MEAS\dmd\LDC HV\Intra 2012"
inputfilename = "WeeklyAveHV2012.xls"
inputtabname = InputBox("Enter the tab which you want to copy" & "In Format MMMWhole, For Example: JunWhole")

newtabname = inputtabname


If Len(newtabname) = 0 Then
MsgBox "No file name chosen"
Exit Sub
End If

Set controlfile = ActiveWorkbook

Set wb = Workbooks.Open(filename:=inputpathname & "\" & inputfilename, UpdateLinks:=0)

wb.Sheets(inputtabname).Copy After:=controlfile.Sheets(1)
wb.Close savechanges:=False


'ActiveSheet.Name = newtabname

controlfile.Sheets(1).Range("B4") = "Completed"

ThisWorkbook.Close

End Sub

mancubus
09-21-2012, 05:43 PM
hi.

insert this line;
ActiveSheet.Name = "Whole"

after;
wb.Sheets(inputtabname).Copy After:=controlfile.Sheets(1)

shrivallabha
09-22-2012, 02:14 AM
Or alternatively add this line
controlfile.Sheets(2).Name = "Whole"
before
controlfile.Sheets(1).Range("B4") = "Completed"