Forum Discussion
JoAvg
Jan 20, 2023Brass Contributor
Macro - Help defining range from two worksheets
I have a macro that creates a "station.xlsx" file inside a given folder in Desktop\AsBuilt\Date\City\Address I need to modify the macro so that: 1. The copied range is derived from STASH!A53:I5...
- Jan 20, 2023
Does this work as desired?
Sub Create_Station() Dim wbkT As Workbook Dim wsStash As Worksheet Dim wsFormulas As Worksheet 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 Application.ScreenUpdating = False Set wsStash = ThisWorkbook.Worksheets("STASH") Set wsFormulas = ThisWorkbook.Worksheets("FORMULAS") Set wbkT = Workbooks.Add(xlWBATWorksheet) Set wshT = wbkT.Worksheets(1) wsStash.Range("A53:I54").Copy wshT.Range("A1").PasteSpecial Paste:=xlPasteValues wshT.Range("A1").PasteSpecial Paste:=xlPasteFormats sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") sDate = Format(Date, "dd mm yyyy") sPath = sPath & "\ASBUILT\" & sDate sCity = wsFormulas.Range("B42").Value sPath = sPath & "\" & sCity sAddress = wsFormulas.Range("B43").Value sPath = sPath & "\" & sAddress sFile = "STATION.xlsx" sPath = sPath & "\" & sFile wbkT.SaveAs Filename:=sPath, FileFormat:=xlOpenXMLWorkbook wbkT.Close Application.ScreenUpdating = True End Sub
JoAvg
Jan 20, 2023Brass Contributor
Yes, it works exactly as expected thank you!
Subodh_Tiwari_sktneer
Jan 20, 2023Silver Contributor
You're welcome JoAvg! Glad it worked as desired.