Forum Discussion
Solved
MACRO - Create Folder/Subfolder based on Today and single cell values - Create *.xlsx in that folder
Hello everyone, newcomer here. I have a book with two main sheets: Overview with ref's to sheet Formulas which is a bunch of index/lookup/search functions. I am in need of two macros in Overview sh...
Sub Macro2() Dim wbkT As Workbook Dim wshS As Worksheet Dim wshT As Worksheet Dim sPath As String Dim sDate As String Dim sCity As String Dim sAddress As String Dim sFile As String Set wshS = ThisWorkbook.Worksheets("Formulas") Set wbkT = Workbooks.Add(xlWBATWorksheet) Set wshT = wbkT.Worksheets(1) wshT.Range("A53:I54").Value = wshS.Range("A53:I54").Value sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") sDate = Format(Date, "dd mm yyyy") sPath = sPath & "\Delivery\" & sDate sCity = wshS.Range("A5").Value sPath = sPath & "\" & sCity sAddress = wshS.Range("A6").Value sPath = sPath & "\" & sAddress sFile = "Station.xlsx" sPath = sPath & "\" & sFile wbkT.SaveAs Filename:=sPath, FileFormat:=xlOpenXMLWorkbook wbkT.Close End Sub
Sub Macro2()
Dim wbkT As Workbook
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim sPath As String
Dim sDate As String
Dim sCity As String
Dim sAddress As String
Dim sFile As String
Set wshS = ThisWorkbook.Worksheets("Formulas")
Set wbkT = Workbooks.Add(xlWBATWorksheet)
Set wshT = wbkT.Worksheets(1)
wshT.Range("A53:I54").Value = wshS.Range("A53:I54").Value
sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
sDate = Format(Date, "dd mm yyyy")
sPath = sPath & "\Delivery\" & sDate
sCity = wshS.Range("A5").Value
sPath = sPath & "\" & sCity
sAddress = wshS.Range("A6").Value
sPath = sPath & "\" & sAddress
sFile = "Station.xlsx"
sPath = sPath & "\" & sFile
wbkT.SaveAs Filename:=sPath, FileFormat:=xlOpenXMLWorkbook
wbkT.Close
End SubHello Hans,
If possible, I would like a slight modification to the second code.
The code copies and pastes the range at the same place: i.e. A53:I54.
Any chance it pastes at A1?
Also, if possible, can the formatting also be pasted?
Thank you again.
Change the line
wshT.Range("A53:I54").Value = wshS.Range("A53:I54").Valueto
wshS.Range("A53:I54").Copy wshT.Range("A1").PasteSpecial Paste:=xlPasteValues wshT.Range("A1").PasteSpecial Paste:=xlPasteFormats
- These worked both like a charm.
Thank you a ton!!!- Great
