Macro too export data minus functions .

[Sunline]

Hello all , i am building a w/book (Book1) which has 170,000 rows and growing weekly .
In this workbook (sheet1) there are functions , i want to export the entire sheet from A1 too KZ170,000 .
Is there anyway of doing this without having to have the same functions too accept what comes from export data into Book2 , sheet1 .
Currently having to export to Access and back but time consuming .
Using 2007 version . Just wondering if theres any easier way .
Thanks .

 

[Michael M]

Are you still getting an error ??
I think you are doing something wrong with the sheet names
where is the error line now ??

The code works fine for me, so if it's not an obvious error, send me a PM and I'll give you my e-Mail address.
If the data isn't sensitive, you can send me a copy of the workbook and I'll take a closer look.

 

[Michael M]

Try this, it works OK for me.
I noticed you were missing a quotation mark in the file name

Private Sub sendsheet() 'EXPORT MASTER SHEET TO NEW WORKBOOK
Application.ScreenUpdating = False
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "1A MAINSHEET 30 BACK VALUES" 'change sheet name to suit
Sheets("Sheet1").Cells.Copy

THIS AREA BELOW IS HIGHLIGHTING IN YELLOW SO AM GUESSING A PROBLEM . 

Sheets("1A MAINSHEET 30 BACK VALUES").Cells.PasteSpecial Paste:=xlPasteFormats
Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Worksheets("1A MAINSHEET 30 BACK VALUES").SaveAs "C:\Documents and Settings\kevin russell watt\My Documents\" & "1A MAINSHEET 30 BACK VALUES" ' change path and filename to suit
Application.ScreenUpdating = True
MsgBox "Master Sheet Successfully Exported to C:\Temp"
End Sub

 

[Sunline]

It worked this time , my golly i have been stressing , thought id have to cut and paste 170,000 rows . Did the lot in one go .
Thankyou for hanging in there for me .
I have looked all over here and web for basic export but everyone elses always has a curly bit extra they want .
Thanks again Michael for your patience and help .

 

[Michael M]

My pleasure, thanks for the feedback.
Something to keep in mind is that Syntax is "REALLY" important in writing VBA.
The tiniest error in adding a comma, quotation mark, spelling etc......can drive you nuts trying to find and rectify.
Cheers