save worksheets by day to day date

[ikshanu]

hi,
My macro is given below

Set wkbSource = ActiveWorkbook
Set wkbDest = Workbooks.Open("d:\test\workbook1.xlsm")
Set shtToCopy = wkbSource.Sheets("sheet2")
shtToCopy.UsedRange.Copy
wkbDest.Sheets(1).Range("A1").PasteSpecial xlPasteValues
wkbDest.Sheets(1).Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
ActiveSheet.Name = Format(Now(), "dd-mm-yy")
ActiveWorkbook.Save
ActiveWorkbook.Close

My requirement is ....
When run to my program every day ,I need to save work sheet by date wise (work book location specified in second raw of program)

 

[DanteAmor]

Do you need something like this?

Sub tst()
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set wkbSource = ActiveWorkbook
  Set wkbDest = Workbooks.Open("d:\test\workbook1.xlsm")
  
  Set shtToCopy = wkbSource.Sheets("sheet2")
  shtToCopy.UsedRange.Copy
  wkbDest.Sheets(1).Range("A1").PasteSpecial xlPasteValues
  wkbDest.Sheets(1).Range("A1").PasteSpecial xlPasteFormats
  Application.CutCopyMode = False
  ActiveSheet.Name = Format(Now(), "dd-mm-yy")
  ActiveWorkbook.SaveAs Format(Now(), "dd-mm-yy") & ".xlsx", xlOpenXMLWorkbook
  ActiveWorkbook.Close
End Sub