Hi All
I am having a small issue and I'm looking for a bit of help. Please see Code below.
It runs correctly but there are a few changes I think that bring up errors when opening that export file, see attached image. The file needs to be pasted in the new file as pasted Special "Values" and needs to be saved as an XLS.
The second thing I need to copy is a hidden worksheet from the same file to the new XLS file,
I would appreciate any help. If I'm not explaining myself clearly please ask.
Thanks in advance
Adam
Sub ExportRangetoExcel()
Dim wb As Workbook
Dim saveFile As String
Dim fileName As String
Dim WorkRng As Range
Dim address As String
Dim defult As Integer
On Error Resume Next
fileName = Sheets("Generate New Tool").Range("L2").Value
xTitleId = "Save New Tool"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
defult = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = defult
ActiveSheet.Name = "TASK"
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(InitialFileName:=fileName, fileFilter:="Excel Workbooks (*.xls),*.xls")
wb.SaveAs fileName:=saveFile
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Bookmarks