Exporting to another workbook

[Dave Punky]

Hi all,

This may sound simple but I'm having problems doing this.

I basically need to transfer one worksheets from my workbook into a new document using a VBA macro. The worksheet has forumlas, which I would not want crossed over, rather the data thats in the cell stays there (so instead of =TODAY() it has 26.01.10).

What is the best way of doing this, as it's the last thing I need to do and I'm finding it hard to find a way around this. Any help would be appreciated.

 

[*shudder*]

Hi Dave,

Try using this in your code

.PasteSpecial Paste:=xlPasteValues

HTH

Stuart

 

[Dave Punky]

Alright that's ideal, the only thing now is actually creating the new document using the macro. That will be good for some of my other macros as well looking at it though!

Any ideas how I could get it to export, even just to save as a new workbook really but with only the one worksheet and the data rather than the formulas?

 

[*shudder*]

Dave,

I am fairly new to vba myself, I have compiled this from web sources and there is more than likely a shorter version.

Sub Copy_Sheet()
Dim wb As Workbook, sh As Worksheet
ActiveSheet.Copy
Set wb = ActiveWorkbook
For Each sh In wb.Worksheets
sh.Cells.Copy
sh.Cells.PasteSpecial xlValues
Next
wb.SaveAs ActiveWorkbook.Path & "New Report.xls"
'or you can name a cell to get the value from wb.SaveAs ActiveWorkbook.Path & Range("A2").Text
End Sub

HTH

Stuart

 

[Dave Punky]

Stuart,

This is exactly what I needed, thank you so much, I've finally managed to finish my spreadsheet!

 

[Dave Punky]

One thing I've noticed just while checking, I'm opting for saving as a cell name, but it's saving as a text document. This isn't so much a problem as it opens the copy anyway to be resaved, but in your opinion what should I add so it saves as an .xls?

EDIT: I see where I'm going wrong with that first bit, wondering why my machine didn't recognize what a .10 file was, realized my date for saving is 27.01.10!

EDIT2: I have conditional formatting setting the date, causing a problem as I can't fit ".xls" in without it killing the formatting, how would I get around that?

Also, it seems to think the active workbook path is My Documents, not sure if thats something to do with my autosave but its in a seperate location. Also can I add a directory to active directory (ie /archive/New Report.xls) - again not a major issue if your not sure, just trying to learn!

 

[*shudder*]

Dave,

Try this:

wb.SaveAs "C:\archive\" & Range("A2").Text & ".xls"

Stuart