copy sheet paste values only

[rjmdc]

i am doing something wrong
it creates 2 new workbooks not 1, one with the first table and one with the other
then it fails onthe save
dont comment on the call refresh that part works

i want 1 copy of the worksheet and paste and save

Sub ExportFile()
    Dim wb                  As New Workbook
    Dim SaveFolder1         As String:          SaveFolder1 = "M:\my folder\Complete\"
    Dim FileName            As String
    Dim FileDateTime        As String:          FileDateTime = Format(Now, "m-d-yy hh-mm")
    Dim FullFileName        As String
    Dim tbl                 As ListObject

Call RefreshBook
   
        Worksheets("Check").Copy
        FileName = "Check " & Replace(Range("G3"), "/", ".")
        FullFileName = Replace(FileName, ".xlsx", "") ' & " (" & FileDateTime & ")"
    
    Set wb = Workbooks.Add it creates 2 books
    With wb
        .Activate
        .Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues it fails here
        .Sheets(1).Name = "Check"
        Selection.Columns.AutoFit
    End With

    'Save and Close New WB
    wb.SaveAs SaveFolder1 & FullFileName & ".xlsx", xlOpenXMLWorkbook
    wb.Close SaveChanges:=False
    DoEvents

    'Back to Main WB
    ThisWorkbook.Activate
    
    MsgBox "Process Complete"

End Sub

 

[rjmdc]

this was perfect thank you
now next issue with this book
copy specific sheets only not just 1
do i copy paste each one separately or all at once
ex copy sheets check, data, match, fraud

 

[rjmdc]

thanks i am failing now as i need to copy
sheets2 -7
what do i need to add to the code to make it work

 

[DanteAmor]

this was perfect thank you
now next issue with this book
copy specific sheets only not just 1
do i copy paste each one separately or all at once
ex copy sheets check, data, match, fraud

They are comments that you must add in your original post.
Now, are you asking me if you want to copy the sheets together or separately?
It is something that you must specify.
You should close this thread and create a new one with the specifications of what you need.

 

[rjmdc]

thanks you
i appreciate your help
1 sheet copies perfectly