copu sheet and close workbook without saving

[blueozzie]

Hi guys,

I'm stuck..

I'm trying to copy one sheet into a new workbook and save this as a variable name ansd then close the original workboo kwithout saving..

Here's where I am and it's not working

Set wb = ThisWorkbook
  
Sheet16.Copy

wb.Activate

Application.DisplayAlerts = False
    ThisWorkbook.Saved = True
wb.Close

filename = accno & ".csv"

    DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs DTAddress & filename, FileFormat:=xlCSV
    Application.DisplayAlerts = True

Any help would be appreciated.


Thanks,

 

[NdNoviceHlp]

Hi blueozzie and Welcome to the Board! Not real sure what "accno" is? HTH. Dave
edit forgot the close wb part. Will repost

 

[NdNoviceHlp]

blueozzie do you want the new wb open? Dave

 

[blueozzie]

hiya,

Sorry, accno is a variable number which will be the filename. I would like otigibsl workbook yo be closed and new workbook to stay open..

 

[NdNoviceHlp]

This seems to work. Dave

Dim accno As Double

Set wb = ThisWorkbook
Sheets("Sheet16").Copy

accno = "1234"
Filename = CStr(accno) & ".csv"

DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
Application.DisplayAlerts = False
wb.SaveAs DTAddress & Filename, FileFormat:=xlCSV
ActiveWorkbook.Close
Application.DisplayAlerts = True
Set DTAddress = Nothing

 

[blueozzie]

Hi Dave,

Thanks, this almost worked, it ended up saving the original workbook as accno and not the new workbook but I fiddled around wth it and got it to work. I have DTAddress set as a string so the set DTAddress = nothing threw up an error. I've removed this line so it works, is it important??


Cheers,

 

[NdNoviceHlp]

Glad you got it sorted out. No need for that line of code if DTAddress is a string. Thanks for posting your outcome. Dave