Export Sheet to new Workbook

[spaz9876]

How do you create a macro to export a sheet to a new workbook?
I have a price list that when someone puts in quantities for parts and filters the quantity column to "non blanks" they have a quote to send. The price list is so big that I would like to be able to export the final quote out to a new workbook.

[Norie]

This will create a new workbook with only the active sheet in it.


Activesheet.Copy

[spaz9876]

Thats all the code I need? Do I put it under modules, thisworkbook or the actual sheet?

[Norie]

Where you put it depends on exactly what you want to do.

eg Do you want to create a new workbook by pressing a button?

[spaz9876]

Yes a button would be great!

[mdmackillop]

Copying the worksheet will retain all the hidden rows. The following will copy the sheet to a new workbook, delete all rows which are blank in column D, and prompt you for a save name for the amended workbook. Columns("D:D") could be changed to a more specific range eg Range("D10:D5000") if there is a header area at the top to be maintained.


Sub CopySheetAndDeleteRows()
ActiveSheet.Copy
Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveWorkbook.SaveAs InputBox("File Name")
ActiveWindow.Close
End Sub

[spaz9876]

Ok That worked GREAT! Thank you SO much!
Now is there a way to import that file back in? Lets say the quote was accepted. We would need to print out the forms that show the margin/profit/etc. Would importing it mess up the formulas already there?

[mdmackillop]

Are you emailing the Excel sheet to your customer? If so, you probably want this with no formulae etc., which is easily accomplished. There are a few ways of dealing with your print out.
Keep a copy of your exported form intact.
Import the data from the exported form into a template
Add the pricing data to the exported form
etc.
If you could post a sanitized copy of your spreadsheet (or part of it), it would help decide the best way forward.
Regards
MD

[mdmackillop]

A further thought,
Although we're quite happy to help you here, if this is a proper business setup, I think you should consider an Access database, which can manage client lists, stock control, invoicing etc within one application. A fair amount of work/learning to set up, but worth examining the Aceess sample files, if you have them.

[spaz9876]

Thanks so much for all your help. I would love to put it in a database but with all the formulas in the background, I don't know how to start.
We do you Access for other things - just not this price list (yet).

And yes, when it exports, it says ='[Client Pricing, 06-29-05, LEF.xls]C-SEP2'!H5052 in the cell. I would like it to show the format (which it currently does) but the values without the formulas.

[mdmackillop]

You would need to do this, in this case, as the recipient would not have the source files, and hence no data

Sub CopySheetAndDeleteRows()
Application.ScreenUpdating = False
ActiveSheet.Copy
Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
[A1].Select
ActiveWorkbook.SaveAs InputBox("File Name")
ActiveWindow.Close
Application.ScreenUpdating = True
End Sub

[spaz9876]

That worked - Thanks!

[spaz9876]

I am getting a debugging error when trying to run this now.
The information cannot be pasted because the Copy area and the paste area are not the same size and shape.
Do I need a code to first paste special - column widths and then paste special-values?
If so what would that code be?

[spaz9876]

In addition, its copying everything outside of the print area as well.

[mdmackillop]

Try the following variation

Sub CopySheetAndDeleteRows()
Application.ScreenUpdating = False
ActiveSheet.Copy
On Error Resume Next
Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("Print_Area").Copy
ActiveSheet.PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.SaveAs InputBox("File Name")
ActiveWindow.Close
Application.ScreenUpdating = True
End Sub

[spaz9876]

It is still copying every column and not just the print area. And its copying all blank cells and not deleting them. The weird thing is that it used to work when you first posted this in June!

[mdmackillop]

Can you email me a copy of a sheet so that I can see your setup?
Send it to eemdm at yahoo dot co dot uk

[spaz9876]

It is a 12kb file of our full price list. I could get in lots of trouble for sending it out. I think I just got it working. Thanks!

[mdmackillop]

Hi,
Like any posting, we don't need (or particularly want) real data, names or products, but the data type etc. should be correct if it is involved in the code. Other problems can arise from merged cells etc. so the layout is also required.
Regards
MD

[Zack Barresse]

Beware of the merged cells when copy/pasting values..