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]

Hi Sunline
Are you saying you want to export the sheet as "values Only" ?
If so, this will export the "new Sheet" to a seperate file location as values only

Private Sub sendsheet() 'EXPORT MASTER SHEET TO NEW WORKBOOK
Application.ScreenUpdating = False
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
   ActiveSheet.Name = "New Sheet" 'change sheet name to suit
Sheets("Sheet1").Cells.Copy
    Sheets("New Sheet").Cells.PasteSpecial Paste:=xlPasteFormats
    Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Worksheets("New Sheet").SaveAs "C:\Temp\" & "New Sheet" ' change path and filename to suit
Application.ScreenUpdating = True
    MsgBox "Master Sheet Successfully Exported to C:\Temp"
End Sub

 

[Sunline]

Hello Michael , um yes , i say um because , hopefully "values Only" covers the following , Im wanting too export normal text , numbers etc and the answers that functions have provided after scrolling down cols etc , but not the functions themselves .
Thanks will give this a try .

 

[Sunline]

Wow , thanks Michael , this has saved me alot and i mean ALOT of hours cutting and pasting .
Am learning so much from here .

Thankyou .

 

[Michael M]

Glad it was what you wanted. Thanks for the feedback.

 

[Sunline]

Hello Michael , spoke to soon again .
Having trouble running this . have chopped w/book to 64,999 rows .
Not sure why its stopped working for me .
When i enter View Macros on the tab bar up top theres nothing there .
Secondly last week when i ran it it did show an error but cant remember what is was but it changed the w/book to values which i thought great as thats what i want rather than having to export , just want to change all enclosed functions (sheet) too values (including text) minus functions .

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("New Sheet").Cells.PasteSpecial Paste:=xlPasteFormats
Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Worksheets("New Sheet").SaveAs "C:\Documents and Settings\kevin russell watt\My Documents" & "New Sheet1" ' change path and filename to suit
Application.ScreenUpdating = True
MsgBox "Master Sheet Successfully Exported to C:\Temp"
End Sub

Not sure what im doing wrong , have tried it over 20 times over weekend .
Thanks .

 

[Michael M]

You need the change the "NewSheet" name to the correct 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]

Thanks for that , i have changed the things you said mentioned and path name as in your original post , now getting error again (see below) . Golly im feeling dum , thanks for your help .



Private Sub sendsheet() 'EXPORT MASTER SHEET TO NEW WORKBOOK
Application.ScreenUpdating = False
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)

Below line now highlighting in yellow .

ActiveSheet.Name = "Copy of 1A MAINSHEET 30 BACK VALUES" 'change sheet name to suit
Sheets("Sheet1").Cells.Copy
Sheets("Copy of 1A MAINSHEET 30 BACK VALUES").Cells.PasteSpecial Paste:=xlPasteFormats
Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Worksheets("Copy of 1A MAINSHEET 30 BACK VALUES").SaveAs "C:\Temp\" & "New Sheet" ' change path and filename to suit
Application.ScreenUpdating = True
MsgBox "Master Sheet Successfully Exported to C:\Temp"
End Sub

 

[Michael M]

Have a look at post #7
I have already changed the name for you, you can't use "Copy of" in the sheet name

 

[Sunline]

Have made changes you recommend but still unable to get working again .
Thanks for your help anyway .