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
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
Rich (BB code):
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