HELP with VBA modification

[JoAv]

Hello everyone,
I need some help in recompiling this code.
It is meant to copy a range from a sheet, paste the range in a new sheet and save it at a given directory.
It works as expected, except that it copies and pasted the range at A53:I54, while I want it to be pasted at A1.
Plus, if possible I would like the formatting to be pasted as well.

Sub New_Sheet()
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

Thank you very much.

 

[rlv01]

Try replacing this line:

    wshT.Range("A53:I54").Value = wshS.Range("A53:I54").Value

with these lines:

    wshS.Range("A53:I54").Copy
    wshT.Range("A1").PasteSpecial Paste:=xlPasteValues
    wshT.Range("A1").PasteSpecial Paste:=xlPasteFormats

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

it makes the code easier to read.)

 

[JoAv]

Try replacing this line:

    wshT.Range("A53:I54").Value = wshS.Range("A53:I54").Value

with these lines:

    wshS.Range("A53:I54").Copy
    wshT.Range("A1").PasteSpecial Paste:=xlPasteValues
    wshT.Range("A1").PasteSpecial Paste:=xlPasteFormats

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

it makes the code easier to read.)

Worked perfect!
Thanks a ton!!
Will keep the code tags in next post in mind!!