Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheets as filenames
Hi everyone, this sort of relates to an earlier message, but I have tried to simplfy what I am asking about (more for my benefit than yours!) :) I have a spreadsheet with a number of worksheets, eg: Name1, Name2, Name3. These names are held in worksheet "AllNames" between cells A1:A10 What I would like to do is loop thru the list of names in "AllNames" A1:A10 and for each inidividual worksheet save the relevant worksheet as a new workbook file in the same filepath but a folder called "Names". If possible with the date it was saved hard coded in. Is this possible?? If so, all help gratefully received Love Amy xx -- AmyTaylor ------------------------------------------------------------------------ AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970 View this thread: http://www.excelforum.com/showthread...hreadid=536051 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheets as filenames
For Each cell In Range("AllNames")
worksheets(cell.Value).Copy Activeworkbook.SaveAs "Names\" & _ Format(Date,"yyy-mm-dd hh:mm:ss") & ".xls" Next cell AmyTaylor wrote: Hi everyone, this sort of relates to an earlier message, but I have tried to simplfy what I am asking about (more for my benefit than yours!) :) I have a spreadsheet with a number of worksheets, eg: Name1, Name2, Name3. These names are held in worksheet "AllNames" between cells A1:A10 What I would like to do is loop thru the list of names in "AllNames" A1:A10 and for each inidividual worksheet save the relevant worksheet as a new workbook file in the same filepath but a folder called "Names". If possible with the date it was saved hard coded in. Is this possible?? If so, all help gratefully received Love Amy xx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheets as filenames
Sub SaveSheetNames()
Dim i As Integer Dim File1 As Workbook Dim Path1 As String Set File1 = ActiveWorkbook Sheets("AllNames").Activate Path1 = File1.Path & "\Names" If Not FSO.FolderExists(Path1) Then FSO.CreateFolder (Path1) For i = 1 To 10 Sheets(Cells(i, 1).Value).Copy ActiveWorkbook.SaveAs Filename:=Path1 & "\" & Cells(i, 1).Value & Replace(Date, "/", "-") _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWorkbook.Close False File1.Activate Sheets("AllNames").Activate Next End Sub There's my 2 minute attempt at it. Note there is NO error checking so make sure that the AllNames sheet range A1:A10 all contain valid sheet names Die_Another_Day |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
All worksheets in document are not saving! | Excel Discussion (Misc queries) | |||
Saving Excel worksheets | Excel Discussion (Misc queries) | |||
Saving worksheets | Excel Worksheet Functions | |||
Saving Worksheets | Excel Programming | |||
Saving worksheets without Auto_Open | Excel Programming |