PDA

View Full Version : Save sheet as a separate file based on cell vale of main sheet



sujittalukde
08-07-2007, 04:20 AM
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


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


Can someone please help me on sorting out the problem?

austenr
08-07-2007, 06:10 AM
So you just want to add a sheet, not a WB?

sujittalukde
08-07-2007, 06:17 AM
No, not want to add a sheet. This I am already doing. I want to save a sheet (named on cell A1 of A sheet named "Import") as a separate file.

Bob Phillips
08-07-2007, 06:25 AM
Then just do a Copy, not a Copy Before, and save the activeworkbook.