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
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