Saving on C:\

[Guest]

Hi to all!

I'm also most completed my project and would like to thank all of you who help,THANK YOU.

Right now I'm trying to save three sheets.

I need to save
SheetA in C:\ABC\A\SheetA,
SheetB in C:\ABC\B\SheetB,
SheetC in C:\ABC\C\SheetC.

I'm using a button to do this and I'm also working with ExcelXP.

Thanks in advance to all.

Cheers

Ren

 

[Bernie Deitrick]

Ren,

Do you have three worksheets in one workbook? Do you want to save them as
separate files? Are there formulas or links involved, or do you want to save
just values? Do you want to save copies? What do you want to have happen to
the original file?

HTH,
Bernie
MS Excel MVP

 

[keepITcool]

Try this:

Sub test()
Dim ws As Worksheet, sName As String
For Each ws In ThisWorkbook.Worksheets
ws.Copy
sName = "c:\abc\" & _
Trim(Replace(ws.Name, "Sheet", "")) & "\" & _
ws.Name & ".xls"
ActiveWorkbook.Close True, sName
Next
End Sub

You may have to add some tests that the directories exists..


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Cecilkumara Fernando]

Ren,
If folder ABC and subfolders A,B,C is already there
Try
Sub aaa()
Dim i As Long
For i = 1 To 3
Sheets("Sheet" & Chr(64 + i)).Select
Sheets("Sheet" & Chr(64 + i)).Copy
ActiveWorkbook.SaveAs _
Filename:="C:\ABC\" & Chr(i + 64) & "\" & _
"Sheet" & Chr(64 + i) & ".xls", _
FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
Next i
End Sub
Cecil

 

[Harald Staff]

Hi Ren

Use keepitcool's advice to copy and save single sheets to separate files.
But note that Excel XP may involve

<...the horror> WINDOWS XP </...the horror>

On lots of setups users are not allowed to do anything anywhere but in
C:\Documents and setings\<user name>\My Documents\ , so your code may err
because the os hates users.

Best wishes Harald