Give a sheet a name from data in a cell-saving to a specific folde

[Chris Maddogz]

I have a blank worksheet (call it no name) and want to use the data in cell
E11 (e.g.data could be 18208C) to rename the worksheet (eg to 18208C)

I then want to save the workbook with the same name as the new worksheet's
name (ie as an example still 18208C) in a subfolder of a Folder called Jobs
on my C drive.

The proviso is that the subfolder name within the Jobs Folder is always the
first 3 digits of a workbook name & if the Subfolder Name (ie in this example
182) within the Jobs Folder doesn't already exist I need to create a new one
using those three digits before saving the workbook
Thanks again

 

[Jacob Skaria]

Hi Chris

Try the below and feedback..(untested)

Sub Macro()
Dim strName As String
Dim strPath As String
Dim strFolder As String
Dim blnExist As Boolean

blnExist = False
strName = Left(Trim(Range("E11")), 3)
strPath = "c:\Jobs\"
strFolder = Dir(strPath & strName & "*", vbDirectory)
Do While strFolder <> ""
If (GetAttr(strPath & strFolder) And vbDirectory) = vbDirectory Then
blnExist = True: Exit Do
End If
strFolder = Dir()
Loop
If blnExist = False Then MkDir "c:\jobs\" & strName
ActiveSheet.Name = Trim(Range("E11"))
ActiveWorkbook.SaveAs "c:\jobs\" & strName & "\" & Trim(Range("E11")) & "xls"
End Sub

 

[Jacob Skaria]

Chris, Initially I thought your subfolder names starts with the first 3
digits and hence the loop...You dont need to loop...instead try the below

Sub Macro()
Dim strName As String
Dim strPath As String
Dim strFolder As String

strName = Left(Trim(Range("E11")), 3)
strPath = "c:\Jobs\"
If Dir(strPath & strName, vbDirectory) = "" Then
MkDir "c:\jobs\" & strName
End If
ActiveSheet.Name = Trim(Range("E11"))
ActiveWorkbook.SaveAs "c:\jobs\" & strName & "\" & Trim(Range("E11")) & ".xls"
End Sub

If this post helps click Yes

 

[Chris Maddogz]

They both worked a treat certainly saved a lot of manual save as etc.