Another way to copy data and create new workbook and paste it

Another way to copy data and create new workbook and paste it






Sub CopyItOver()
  Set Newbook = Workbooks.Add
  Workbooks("CENTRUM REPLENISHMENT TEMPLATE.xlsm").Worksheets("Master Order Template").Copy
  Newbook.Worksheets("Sheet1").PasteSpecial Paste:=xlPasteValues
 ' NewBook.SaveAs Filename:=NewBook.Worksheets("Sheet1").Range("E3").Value
   'ThisWorkbook.Close Savechanges = False
End Sub

Sub CopyOver2()
Dim newWB As Workbook, currentWB As Workbook
Dim newS As Worksheet, currentS As Worksheet
Dim xStrDate As String
Dim xFileName As String
Dim xJBName As String
Dim xLocation As String

'Copy the data you need
'Set currentWB = ThisWorkbook
Set currentS = Workbooks("CENTRUM REPLENISHMENT TEMPLATE.xlsm").Sheets("MASTER ORDER TEMPLATE")

currentS.Range("A:AT").Select
Selection.Copy

'Create a new file that will receive the data
xStrDate = Format(Now, "mmdd_hhmm")
xJBName = Workbooks("CENTRUM REPLENISHMENT TEMPLATE.xlsm").Sheets("Store Input").Range("B9").Value & "_"
xLocation = Workbooks("CENTRUM REPLENISHMENT TEMPLATE.xlsm").Sheets("Store Input").Range("$J$24").Value
Set newWB = Workbooks.Add
    With newWB
        Set newS = newWB.Sheets("Sheet1")
        newS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        newS.Range("A1").PasteSpecial Paste:=xlPasteFormats
        Rows("1:10").EntireRow.Hidden = True
        ActiveSheet.Name = Workbooks("CENTRUM REPLENISHMENT TEMPLATE.xlsm").Sheets("Store Input").Range("J30").Value
        'Save in CSV
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:=xLocation & "Order Template\" & xJBName & xStrDate, FileFormat:=xlWorkbookNormal
        Application.DisplayAlerts = True
      ActiveWorkbook.Close Savechanges:=True

'ThisWorkbook.Show = False
        End With
'SendWorkBook


End Sub