Save Macro

[superfb]

Hi

I have this code to save a spreadsheet,

Sub Save()'
' Save Macro


    Sheets(Array("Sheet1")).Select
    Sheets(Array("Sheet1")).Copy
    
    ActiveWorkbook.SaveAs Filename:=Range("F21")
    Range("A1").Select
    
End Sub

[TABLE="width: 215"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] '=CONCATENATE("\\ac-eu\uk\corp\Work","Weekly MI Report"," ",(Day)," ",(Month)," 2019.xls")

[TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl55050, width: 87, align: right"]19/03/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]Day[/TD]
[TD="align: left"]19[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]Month[/TD]
[TD="align: left"]03[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The above formula is in F21

The problem i have to save this spreadsheet in different folders, depending on the clients name there would be a folder it would go in..........

isit worth having a pop up box and selecting the directory manually, if so could the file name be derived automatically??

 

[silentwolf]

Hi,
what are you trying to do here?
To save a workbook you just have a code like activeworkbook.saveas("YourFileName")
No need to select or copy.
You can or could use the "ScriptingRunTime" Liberary there are functions you need to perform those kind of actions..

HTH

 

[superfb]

what this macro does is copy the tab in to a new spreadsheet and then save it according to the file name in f21.....

my query is is there an easier way to save the file name in a directory....as in this method i would have to kee changing the file directory

 

[silentwolf]

Hi,
so in your worksheet there is always in "F21" the filename you like to have?
And where would be the folder Name or will it be saved always in the same folder but differen file names?
There are always many different ways of doing things it is all depands what you like to achive.
And as more information we have in regards of what you idealy like to have as easier is to come up with the right solution rahter then go forth and back with questions and answers

 

[superfb]

Hi,
so in your worksheet there is always in "F21" the filename you like to have?
And where would be the folder Name or will it be saved always in the same folder but differen file names?
There are always many different ways of doing things it is all depands what you like to achive.
And as more information we have in regards of what you idealy like to have as easier is to come up with the right solution rahter then go forth and back with questions and answers

Thank you for the reply.

i believe the file name will change, and the folder will also change.....

at the moment i keep changing the file directory in F21 which becomes quite cumbersome, so i was trying to understand is there a smoother way of getting the workbook, copy it and then save it according to the F21 file name.........but the folder it goes into would need to change......i understand you can have a dialog box to appear to determine where to save it, but again there would be quite annoying.......

 

[silentwolf]

Hi,
I understand that the filename and the foldername will need to change accordingly.
However will there be in your worksheet a folderpath somewhere?
If you have somewhere in your worksheet a name of your customer then you could create a folder for this customer.
Say you have a customer listed in "A1" and in "F21" is the filename then you could get the macro to create your folder and save the file into that folder.
But to get this to work I would need to know if you have in your worksheet somewhere the customer or folder name.

 

[silentwolf]

Hi,
you could use something like this code below... You just need to adjust to your needs.. )

Option Explicit

'Module varibles
Private m_FolderName As String
Private m_FileName As String

Public Const p_cstrPath = "C:\Users\Albert\Documents\MyCustomers"   'Enter your Path here!!!
'

Property Let FolderName(strName As String)
    m_FolderName = strName
End Property
Property Get FolderName() As String
    FolderName = m_FolderName
End Property

Property Let FileName(strName As String)
    m_FileName = strName
End Property
Property Get FileName() As String
    FileName = m_FileName
End Property

Sub CreateCustomerFolder()
    Dim wksQ As Worksheet
    Dim wbkZ As Workbook
    Dim wksZ As Worksheet

    Dim fso As Scripting.FileSystemObject                 'Reference to Microsoft Scripting Runtime...
    
    Set wksQ = ThisWorkbook.Worksheets("Tabelle1")        'Sheet1 in your english version
    
    FolderName = p_cstrPath & "\" & wksQ.Range("A1").Value
    FileName = wksQ.Range("F21").Value
    Set fso = New Scripting.FileSystemObject
    
    If Not fso.FolderExists(FolderName) Then
        fso.CreateFolder FolderName
    End If
    
    Set wbkZ = Workbooks.Add()
    Set wksZ = ActiveWorkbook.Worksheets(1)
    
    wksQ.UsedRange.Copy wksZ.Cells(1, 1)
    wksZ.Name = FileName
    
    wbkZ.SaveAs FolderName & "\" & FileName
   

    
    Set fso = Nothing
    Set wksQ = Nothing
    Set wksZ = Nothing

End Sub

HTH

 

[superfb]

Hi,
I understand that the filename and the foldername will need to change accordingly.
However will there be in your worksheet a folderpath somewhere?
If you have somewhere in your worksheet a name of your customer then you could create a folder for this customer.
Say you have a customer listed in "A1" and in "F21" is the filename then you could get the macro to create your folder and save the file into that folder.
But to get this to work I would need to know if you have in your worksheet somewhere the customer or folder name.

Hi, thank you for the reply. Yes the worksheet will have the customer name and the filepath will be in a cell!

The problem i have is that the folder name when putting it in the filename there could be a "-" which i dont know about unless i look for the folder specifically and copy the name in to the cell...

 

[silentwolf]

Hi,
have you looked at the code I did provide?
Do you understand that code?