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

  • Thread starter Thread starter Chris Maddogz
  • Start date Start date
C

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
 
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
 
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
 
Back
Top