I admit I am not good with VBA at all, but have pieced together some code from reading other forum threads. I would appreciate any help or suggestions anyone may have!!! 
I have a Command Button on Worksheet 1 of Workbook 1. When pressed, I would like for it to copy the entirety of Worksheet 2 (as values since it contains links to pivot reports) to a new Workbook (eg Workbook 2). I would like for the file name and path to be linked to user entered text in cell U11 in Worksheet 1.
This is my code so far:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim wbTarget As Workbook 'Destination File Name
Dim wsTarget As Worksheet
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Set wbTarget = ActiveWorkbook
Set wsTarget = wbTarget.Worksheets("Sheet1")
Set wbSrc = Workbooks.Add
Set wsSrc = wb.Src.Worksheets("S Datasheet")
wsTarget.Range("A1:A").ClearContents
Application.CutCopyMode = False
wsSrc.Range("A1:A").Copy
wsTarget.Range("A1").PasteSpecial xlPasteAll
Application.CutCopyModeMode = False
wbSrc.Save
wb.Src.Close
Set wbSrc = Nothing
Set wbTarget = Nothing
Application.ScreenUpdating = True
DstFile = Application.GetSaveAsFilename _
(InitialFileName:=ActiveWorkbook.Sheets("Worksheet 1").Range("U11").Value & ".xls", _
Title:="Save As")
If DstFile = "False" Then
MsgBox "File Not Saved, Actions Cancelled."
Exit Sub
Else
wb.SaveAs DstFile 'Save file
wb.Close 'Close file
End If
Workbooks(".xls").Activate
MsgBox ("File Saved at" & DstFile)
Application.ScreenUpdating = True
End Sub
Bookmarks