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
Hello 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- Hey
