Copy worksheet data and formatting to new Workbook, and file error

[Rickinnocal]

Hello, I have a workbook with several sheets. One of the sheets is a summary, the format and layout of which is from the customer. I need to send my customer this summary as a one page workbook.

I've copied the customers form into my workbook, and have it all set up to populate and calculate properly. Now I want a command button that will copy the sheet to a new workbook.

1st problem... I seem to be stuck, though, with either cells in the new workbook still containing formulas referring back to the original workbook, not just the data, or they contain data only but the formatting all vanishes.

This is my latest attempt...

Private Sub cmdMakeBQS_Click()

Dim wsCopy As Worksheet, wsPaste As Worksheet
Dim wb As Workbook
Dim sFileName As String, sPath As String


    'Path to store new file
    sPath = "C:\"
    'Change filename as required
    sFileName = "New BQS"
    
    'set the sheet you are copying. Change where neccessary
    Set wsCopy = ThisWorkbook.Worksheets("Prelim (FO)")
    Set wb = Workbooks.Add
    Set wsPaste = wb.Sheets(1)
    
    'Copy everything from copy sheet
    wsCopy.Cells.Copy
    'Paste Values only
    wsPaste.Cells.PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    
    'Save new workbook
    
    wsPaste.Name = "New BQS" 'Change if needed
    wb.SaveAs Filename:=sPath & sFileName, FileFormat:=xlOpenXMLWorkbook
    
End Sub

If I don't use xlPasteValues I can get the formatting, but it copies formulas.

My second issue is that I get this error message....

The file name is different every time I do it, and I don't know where its coming from.

Any hints, please?

Richard

 

[JLGWhiz]

Try changing this

 'Copy everything from copy sheet
    wsCopy.Cells.Copy
    'Paste Values only
    wsPaste.Cells.PasteSpecial xlPasteValues

to this

 'Copy everything from copy sheet
    wsCopy.UsedRange.Copy
    'Paste Values only
    wsPaste.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    wsPaste.Range('A1").PasteSpecial xlPasteFormats

Before you run the code, be sure to clear the sheet you are pasting to so you don't have remnants of past mistakes embedded. Click ovever the row number column to select all cells then press the deleted key to remove any cell content. Then run the macro.

 

[Rickinnocal]

Thank you, that helped. I had to add one more line for the column widths...

 'Copy everything from copy sheet
    wsCopy.UsedRange.Copy
    'Paste Values only
    wsPaste.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    wsPaste.Range("A1").PasteSpecial xlPasteFormats
    wsPaste.Range("A1").PasteSpecial xlPasteColumnWidths

I'm still plagued by the error box, though. I think my code "should" save the new workbook in C:\ as "New BQS", but it doesn't. it opens as "Book 1" (Increments each time I run it) and the error 1004 gives "cannot access C:\#######" with a different string of numbers and letters each time.

 

[Rickinnocal]

One extra question...

Is there a way to copy the Sheet name to the new workbook as well?

Richard

 

[Rickinnocal]

One extra question...

Is there a way to copy the Sheet name to the new workbook as well?

Richard

Figured it out myself... The new Workbook / Worksheet are active when created, so

ActiveSheet.Name = "Prelim (FO)"

does the job.

 

[JLGWhiz]

Thanks for the feedback,
Regards, JLG