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

Rickinnocal

New Member
Joined
Dec 14, 2010
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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...
VBA Code:
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....

Excel error.jpg


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

Any hints, please?

Richard
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try changing this
VBA Code:
 'Copy everything from copy sheet
    wsCopy.Cells.Copy
    'Paste Values only
    wsPaste.Cells.PasteSpecial xlPasteValues
to this
VBA Code:
 '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.
 
Upvote 0
Solution
Thank you, that helped. I had to add one more line for the column widths...

VBA Code:
 '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.
 
Upvote 0
One extra question...

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

Richard
 
Upvote 0
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
VBA Code:
ActiveSheet.Name = "Prelim (FO)"
does the job.
 
Upvote 0
Thanks for the feedback,
Regards, JLG
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top