Move a worksheet and paste it as values

[waldymar]

Dear All,

I need to copy and move a worksheet and save as values in a new workbook. I'm almost done just beside the pasting as values. I would appreciate much your help on that:

Sub moving()
Dim varPath As String
Dim WbOpen As Workbook
Set WbOpen = Workbooks.Add
varPath = codedraft.Range("b14").Value
datadraft.Copy before:=WbOpen.Sheets(1)
Application.DisplayAlerts = False
WbOpen.SaveAs varPath & "\General-" & Format(Date, "dd-mmm-yyyy") & ".xlsx"
Application.DisplayAlerts = True
WbOpen.Close
End Sub

 

[Sektor]

What are codedraft and datadraft variables?

...
varPath = codedraft.Range("b14").Value
datadraft.Copy before:=WbOpen.Sheets(1)
...

 

[waldymar]

What are codedraft and datadraft variables?

...
varPath = codedraft.Range("b14").Value
datadraft.Copy before:=WbOpen.Sheets(1)
...

they are worksheet names

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] moving()
    [COLOR="Blue"]Dim[/COLOR] varPath [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] WbOpen [COLOR="Blue"]As[/COLOR] Workbook
    [COLOR="Blue"]Set[/COLOR] WbOpen = Workbooks.Add
    varPath = codedraft.Range("b14").Value
    datadraft.UsedRange.Copy
    WbOpen.Sheets(1).PasteSpecial xlPasteValues
    Application.DisplayAlerts = [COLOR="Blue"]False[/COLOR]
    WbOpen.SaveAs varPath & "\General-" & Format([COLOR="Blue"]Date[/COLOR], "dd-mmm-yyyy") & ".xlsx"
    Application.DisplayAlerts = [COLOR="Blue"]True[/COLOR]
    WbOpen.Close
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[waldymar]

[COLOR=blue]Sub[/COLOR] moving()
    [COLOR=blue]Dim[/COLOR] varPath [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    [COLOR=blue]Dim[/COLOR] WbOpen [COLOR=blue]As[/COLOR] Workbook
    [COLOR=blue]Set[/COLOR] WbOpen = Workbooks.Add
    varPath = codedraft.Range("b14").Value
    datadraft.UsedRange.Copy
    WbOpen.Sheets(1).PasteSpecial xlPasteValues
    Application.DisplayAlerts = [COLOR=blue]False[/COLOR]
    WbOpen.SaveAs varPath & "\General-" & Format([COLOR=blue]Date[/COLOR], "dd-mmm-yyyy") & ".xlsx"
    Application.DisplayAlerts = [COLOR=blue]True[/COLOR]
    WbOpen.Close
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

unfortunately, it doesn't work. it gives me a mistake in this row:

WbOpen.Sheets(1).PasteSpecial xlPasteValues

in my original coding, the expression

before:=WbOpen.Sheets(1)

meant that the moved worksheet is paste in a new workbook just before the sheets(1).