Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Durril Jansen   on Jan 11 In MS Office Category.

  
Question Answered By: Botan Suzuki   on Jan 11

Try this

Option Explicit

Sub stuff()
Dim DISTNAME As String 'Better to be more specific
DISTNAME = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "
DIST" & ".xls"
DISTNAME = ActiveWorkbook.Path & Application.PathSeparator & DISTNAME
'Tell it where to put it
Application.DisplayAlerts = False 'Not sure why this is needed
Application.ScreenUpdating = False
Sheets.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
ActiveWorkbook.SaveAs DISTNAME
Application.DisplayAlerts = True 'Tidy up after you finish
Application.ScreenUpdating = True 'Tidy up after you finish

End Sub

If the DIST file  already exists it will fail and not warn you.

Share: 

 
 
Didn't find what you were looking for? Find more on Save file in the same folder as the active workbook? Or get search suggestion and latest updates.


Tagged: