Background:
I have a WB which downloads web data related to stock/shares The stock name is put in cell A1 of ?Import? sheet. By another macro, I am saving the data as a separate sheet in the same WB. Suppose cell A1 contains stock code as ?ABC?. Then the code downloads data related to stock ?ABC?. This data are saved as a a separate sheet.
Now I change the cell A1 of ?Import? sheet to stock code ?XYZ? The data of ?xyz? are downloaded and saved as a separate sheet.
Now my requirement is :
I want to run the code from ?Import? sheet. The code will save the sheet, based on name in cell A1 of ?Import? sheet, as a separate file . Suppose, cell A1 contains ?ABC? it will have to save the ?ABC? sheet and not the ?Import? sheet. Your code is saving the ?Import? sheet now. The name of the file should also be ?ABC?. So that the data of ABC are saved as a separate file.
The following code is saving the activesheet as a separate file and not the sheet named on cell A1 of "Import" sheet
Can someone please help me on sorting out the problem?Option Explicit Sub Extract_Current_Sheet_to_File() Dim ws As Object Dim strName As String Dim strFN As String Set ws = ActiveSheet strName = ws.[a1] strFN = strName & "xls" Application.ScreenUpdating = False Workbooks.Add ws.Copy Before:=Sheets(1) ActiveSheet.Name = strName ActiveWorkbook.SaveAs Filename:=strFN, FileFormat:=xlNormal Workbooks(strFN).Close Application.ScreenUpdating = True End Sub