Copy worksheet to new workbook without formulas or macro button

[Tgrundy09]

Hello,
I need a macro that, after clicking a button, will copy the data on my worksheet to a new workbook without the formulas or macro button. Just the data on the worksheet.
In this example, I have the worksheet tab "Practice" which contains formulas, the macro button and a table of data beginning on A6.

I Need to copy the data from A6 onto a new workbook with the data only. No formulas or buttons:

This file will be shared by multiple people and will be used with varying data every time, so I want the new workbook to simply be a new workbook ("book1", "book2") etc... I don't want to reference any other saved workbook, nor do I want to assign a file location to save the new workbook. Simply enter the data, click the button to copy as text to a new workbook (keeping the table format), and allow the user to save the new file however they would like.

All I currently have is the ability to open a new workbook and copy the info exactly as the current workbook is. My VBA knowledge is very basic, so this is all I have currently:

If I need some sort of range copy and paste special feature, I'm not sure how to do this onto the new workbook.

Thank you in advance!

 

[Micron]

maybe like this in post 4?

Copy worksheet (values only) to new workbook

I have created the following macro to copy a worksheet called Parsed to a new workbook... the problem that i am running into is that it is coping the actual formula's to the new workbook. All i need it to do is copy the values in the worksheet so that the data will show in a new workbook. Sub...

www.mrexcel.com

 

[mumps]

Change the name of your button (in red) to suit your needs.

Sub NewWB()
    Application.ScreenUpdating = False
    Sheets("Practice").Copy
    With ActiveSheet
        .UsedRange.Value = .UsedRange.Value
        .Shapes.Range(Array("Button 1")).Delete
    End With
    Application.ScreenUpdating = True
End Sub

 

[Tgrundy09]

maybe like this in post 4?

Copy worksheet (values only) to new workbook

I have created the following macro to copy a worksheet called Parsed to a new workbook... the problem that i am running into is that it is coping the actual formula's to the new workbook. All i need it to do is copy the values in the worksheet so that the data will show in a new workbook. Sub...

www.mrexcel.com

This worked, using:
Workbooks.Add
Workbooks("Practice Macro.xls").Sheets("Practice").UsedRange.Copy
ActiveSheet.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

However, it did not copy over the table borders. Any other suggestions?

 

[Tgrundy09]

Change the name of your button (in red) to suit your needs.

Sub NewWB()
    Application.ScreenUpdating = False
    Sheets("Practice").Copy
    With ActiveSheet
        .UsedRange.Value = .UsedRange.Value
        .Shapes.Range(Array("Button 1")).Delete
    End With
    Application.ScreenUpdating = True
End Sub

This worked, however it copies the button over and gives me the below error:

 

[mumps]

How did you create the button?

 

[Tgrundy09]

How did you create the button?

Through the developer tab, Insert, Button (Form Control)

 

[Micron]

Sorry but Excel vba isn't my forte - I'm better with M$ Access vba so I would have to Google how to copy table borders (and Google is how I manage to attempt to answer a lot of Excel vba questions). If the table copies over, maybe just add the borders?

It would be the same for me regarding how to solve the error you get (i.e. Googling). I believe that there are two types of buttons (that makes no sense to me) and one seems to be a shape when it's on a sheet and the other is an ActiveX control.

EDIT - I suspect that the new (copied) button name does not carry over. It is probably closer to "CommandButton1". Immediate window should be able to provide the new button name.

 

[Tgrundy09]

This worked, using:
Workbooks.Add
Workbooks("Practice Macro.xls").Sheets("Practice").UsedRange.Copy
ActiveSheet.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

However, it did not copy over the table borders. Any other suggestions?

I got it to work with:

Thank you!

 

[Tgrundy09]

Sorry but Excel vba isn't my forte - I'm better with M$ Access vba so I would have to Google how to copy table borders (and Google is how I manage to attempt to answer a lot of Excel vba questions). If the table copies over, maybe just add the borders?

It would be the same for me regarding how to solve the error you get (i.e. Googling). I believe that there are two types of buttons (that makes no sense to me) and one seems to be a shape when it's on a sheet and the other is an ActiveX control.

EDIT - I suspect that the new (copied) button name does not carry over. It is probably closer to "CommandButton1". Immediate window should be able to provide the new button name.

I got something to work. Thanks for the suggestions!