Simplest Way to Copy Spreadsheet Values and Formatting to New Unsaved Workbook.

[avd88]

Hello,

Hoping someone can help out.
Trying to copy a worksheet to a new unsaved workbook with the same formatting and without any formulas so that a user can save themselves if they chose to do so.

Been searching in the forum and online but I can only find complex solutions.

Trying to find the simplest way to accomplish this, any ideas?

This is my current VBA code which will copy the exact spreadsheet with formulas:

    Dim prov As String
    prov = Me.provComboBox.Text

    Worksheets(prov).Copy

 

[offthelip]

try this:

Dim prov As String
    prov = Me.provComboBox.Text

    Worksheets(prov).Copy
inarr = ActiveSheet.UsedRange
ActiveSheet.UsedRange = inarr

 

[jon1315]

Good afternoon
Could you not just add this to the bottom of your code:

workbooks.add
Range("a1").PasteSpecial xlPasteValues
Range("a1").PasteSpecial xlPasteFormats



Obviously assuming you want pasting in a1
Thanks
Jon

 

[avd88]

offthelip, thanks for your response. It seems to work perfectly!

Jon, thanks for your reply! I am not entirely sure if that code will work with the worksheet.copy method as it immediately opens up a new workbook.
That logic would probably work if I selected and copied the range from the worksheet which is kind of what I was trying to avoid.