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

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
--
If this post helps click Yes
---------------
Jacob Skaria


"Chris Maddogz" wrote:

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

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
---------------
Jacob Skaria


"Chris Maddogz" wrote:

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

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Chris Maddogz" wrote:

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