Export worksheet to new workbook and restore original sheet

[jmaocubo]

Hi

I’ve a workbook with a worksheet that is a standard workseet (mask).
I created a userform with some textboxes and checkboxes.
My problem is that I need the values and texts from the userform to be export to the standard worksheet. Than save as (export to a new workbook only that worksheet) to a specific location on c:\report 2013\ and allow the selection of a folder to save (in that directory).
After that process, the original worksheet is clean of the data.

Thanks in advance

Miguel

[Jacob Hilderbrand]

You can transfer the data like this:

Sheets("Sheet1").Range("A1").Value = Userform1.TextBox1.Text
Sheets("Sheet1").Range("A2").Value = Userform1.CheckBox1.Value

You can copy the sheet to a new workbook with this:

Sheets("Sheet1").Copy

That will copy the sheet to a new workbook.

I would copy it first and then plug the data in on the new sheet, then your original sheet is not modified.

You can then save the workbook.

ActiveWorkbook.SaveAs Filename:="c:\report 2013\test.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

And then close the workbook.

ActiveWorkbook.Close

[jmaocubo]

Hi DRJ

Thanks for the help....

I followed your advice and it works perfectly.

Option Explicit

Private Sub CommandButton1_Click()
    

Dim wb1 As Workbook
Dim savelocation As String


Application.ScreenUpdating = False
    
    Sheets("Folha1").Select
    Sheets("Folha1").Copy



Worksheets("Folha1").Range("B2") = Me.TextBox1.Value
Set wb1 = ThisWorkbook

savelocation = wb1.Worksheets("Folha2").Range("C1")

ActiveWorkbook.SaveAs Filename:=savelocation & "\" & Me.TextBox2.Value, FileFormat:=51

ActiveWorkbook.Close

Application.ScreenUpdating = True

Unload Me


End Sub

[stevenpiers]

I have a simular workbook only with multiple sheets. I can't seem to get it right to copy the sheets as values... Could you please check what I've done wrong?

Dim Bestandsnaam As String
Dim SRPFilePath As String

SRPFilePath = "D:Voorradenoverzicht\Exports Voorraden\"


Sheets(Array("Week 19", "Week 22 --> 29", "Week 23 --> 29", "Week 24", "Week 26 --> 28", "Week 28", "Week 37", "Week 41")).Select
Sheets("BASIS").Activate
Sheets(Array("Week 19", "Week 22 --> 29", "Week 23 --> 29", "Week 24", "Week 26 --> 28", "Week 28", "Week 37", "Week 41")).Copy


Bestandsnaam = "Stock " & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")


ActiveWorkbook.SaveAs SRPFilePath & Bestandsnaam

Thanks in advance!

You can transfer the data like this:

Sheets("Sheet1").Range("A1").Value = Userform1.TextBox1.Text
Sheets("Sheet1").Range("A2").Value = Userform1.CheckBox1.Value

You can copy the sheet to a new workbook with this:

Sheets("Sheet1").Copy

That will copy the sheet to a new workbook.

I would copy it first and then plug the data in on the new sheet, then your original sheet is not modified.

You can then save the workbook.

ActiveWorkbook.SaveAs Filename:="c:\report 2013\test.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

And then close the workbook.

ActiveWorkbook.Close