# copy sheet paste values only



## rjmdc (Jan 3, 2023)

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
```


----------



## DanteAmor (Jan 3, 2023)

This instruction "copies" the sheet:
`Worksheets("Check").Copy`

But it is not actually copying the content of the sheet into *memory*, what it does is create a new book with the sheet, so* the memory is empty*, that's why you have an error when trying to paste, since memory is empty.

To copy the content of the sheet, you must copy the content of the cells, like this:


```
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
  
  FileName = "Check " & Replace(Range("G3"), "/", ".")
  FullFileName = Replace(FileName, ".xlsx", "") ' & " (" & FileDateTime & ")"

  *Worksheets("Check").Cells.Copy    'copy cells to memory*

  Set wb = Workbooks.Add 
  With wb
      .Activate
      .Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues 
      .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 (Jan 3, 2023)

thank you
how do i paste formatting also but not frmulas


----------



## rjmdc (Jan 3, 2023)

its a sheet with 4 tables
it copies the data not the table formats
please assist


----------



## DanteAmor (Jan 3, 2023)

I show you 2 ways:
*First*:

```
Sub ExportFile_1()
  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

  Call RefreshBook
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  FileName = "Check " & Replace(Range("G3"), "/", ".")
  FullFileName = Replace(FileName, ".xlsx", "") ' & " (" & FileDateTime & ")"

  Set wb = Workbooks.Add
  With wb
      .Activate
      ThisWorkbook.Sheets("Check").Cells.Copy .Sheets(1).Range("A1")
      .Sheets(1).Name = "Check"
      .Sheets("Check").Cells.Copy
      .Sheets(1).Range("A1").PasteSpecial xlPasteValues
      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
  
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True

  MsgBox "Process Complete"
End Sub
```


*Second*:


```
Sub ExportFile_2()
  Dim SaveFolder1         As String:          SaveFolder1 = "c:\trabajo\" '"M:\my folder\Complete\"
  Dim FileName            As String
  Dim FileDateTime        As String:          FileDateTime = Format(Now, "m-d-yy hh-mm")
  Dim FullFileName        As String

  Call RefreshBook
   
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  FileName = "Check " & Replace(Range("G3"), "/", ".")
  FullFileName = Replace(FileName, ".xlsx", "") ' & " (" & FileDateTime & ")"

  Sheets("Check").Copy
  ActiveWorkbook.Sheets(1).Cells.Copy
  ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues
  'Save and Close New WB
  
  ActiveWorkbook.SaveAs SaveFolder1 & FullFileName & ".xlsx", xlOpenXMLWorkbook
  ActiveWorkbook.Close SaveChanges:=False
  DoEvents

  'Back to Main WB
  ThisWorkbook.Activate
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  
  MsgBox "Process Complete"
End Sub
```


----------



## DanteAmor (Jan 3, 2023)

*Second*:


```
Sub ExportFile_2()
  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

  Call RefreshBook
   
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  FileName = "Check " & Replace(Range("G3"), "/", ".")
  FullFileName = Replace(FileName, ".xlsx", "") ' & " (" & FileDateTime & ")"

  Sheets("Check").Copy
  ActiveWorkbook.Sheets(1).Cells.Copy
  ActiveWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues
  'Save and Close New WB
  
  ActiveWorkbook.SaveAs SaveFolder1 & FullFileName & ".xlsx", xlOpenXMLWorkbook
  ActiveWorkbook.Close SaveChanges:=False
  DoEvents

  'Back to Main WB
  ThisWorkbook.Activate
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  
  MsgBox "Process Complete"
End Sub
```


----------



## rjmdc (Jan 3, 2023)

hi
now it copied the tables beautifully but also the formulas
how can i stop that

i used metod 1


----------



## DanteAmor (Jan 3, 2023)

Check option 1 again, I did an update.

```
Sub ExportFile_1()
  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

  Call RefreshBook
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  FileName = "Check " & Replace(Range("G3"), "/", ".")
  FullFileName = Replace(FileName, ".xlsx", "") ' & " (" & FileDateTime & ")"

  Set wb = Workbooks.Add
  With wb
      .Activate
      ThisWorkbook.Sheets("Check").Cells.Copy .Sheets(1).Range("A1")
      .Sheets(1).Name = "Check"
      .Sheets(1).Cells.Copy
      .Sheets(1).Range("A1").PasteSpecial xlPasteValues
      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
 
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True

  MsgBox "Process Complete"
End Sub
```


----------



## rjmdc (Jan 3, 2023)

still has formulas


----------



## DanteAmor (Jan 4, 2023)

Check this:



```
Sub ExportFile_1()
  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

  Call RefreshBook
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  FileName = "Check " & Replace(Range("G3"), "/", ".")
  FullFileName = Replace(FileName, ".xlsx", "") ' & " (" & FileDateTime & ")"

  Set wb = Workbooks.Add
  With wb
      .Activate
      ThisWorkbook.Sheets("Check").Cells.Copy .Sheets(1).Range("A1")
      .Sheets(1).Name = "Check"
      .Sheets(1).Cells.Copy
      .Sheets(1).Range("A1").PasteSpecial xlPasteValues
      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
  
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True

  MsgBox "Process Complete"
End Sub
```


----------



## rjmdc (Jan 3, 2023)

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 (Jan 4, 2023)

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 (Jan 4, 2023)

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 (Jan 4, 2023)

rjmdc said:


> 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 (Jan 4, 2023)

thanks you
i appreciate your help
1 sheet copies perfectly


----------

