How to copy just one sheet (not all) to .xls file in Excel VBA?

1
votes

Please Help. I want to copy just one Sheet ‘MainFinal’ among twelve other sheets(not all of the sheets in the original), to another .xls file using Excel VBA . The code I have attached below works , except it copies all worksheets and not JUST the one, and also when new file created it opens and source file is closed . Anyone have any suggestions what wrong with my code? I have tried various combinations of Worksheet, Sheets, Activesheet but without successes)? 

Sub CopyMainFin()
'
' CreaMainFin Macro
'
Dim LastCopyRow As String
Dim MyStr As String
MyStr = Format(Date, "mmddyyyy")
LastCopyRow =  “BT307”

Application.ScreenUpdating = False

Worksheets("MainFinal").Range("A1", LastCopyRow).Activate
Worksheets("MainFinal").Range("A1",LastCopyrow).Select
Worksheets("MainFinal").Range("A1", LastCopyRow).Copy
‘ I noticed that my rage selected

ActiveSheet.SaveAs Filename:="C:\Documents and Settings\algorn\My Documents\Excel files\" & "OutputFile" & MyStr & ".xls", CreateBackup:=False

'ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\My Documents\Excel files\" & "OutputFile" & MyStr & ".xls", CreateBackup:=False     = Also not working

End Sub
1
excelvbacopy

1 Answers

2
votes

This will move the named sheet into a brand new file. No extra coding needed.

Sheets("MainFinal").Copy

This line should save your new file.

Workbooks(workbooks.count).saveas _
    Filename:="C:\Documents and Settings\algorn\My Documents\Excel files\" & _
              "OutputFile" & MyStr & ".xls", CreateBackup:=False