MACRO Save every sheet separatly but text only! with no formulas

[Scavenger102]

Hi

As in the title.

I would like to save every sheet from spread sheet separately as excel files.

The macro I am using does it but I cannot upload the files to my software as the files contain formulas from the original file.

Hence my question, how to save the sheets separately but with text only?

That is what I used

Please Login or Register  to view this content.

[AlphaFrog]

Try this...

Please Login or Register  to view this content.

[CK76]

I'd do it like below using similar concept, which keeps formula in the original, but change SaveAs workbook to values only.

Please Login or Register  to view this content.

[Scavenger102]

That is ok but I just realised that the file still cannot be uploaded as it cannot be read by Notepad. So I changed the file extension to .CSV

Please Login or Register  to view this content.

and the file seems to be "corrupted" -

The file cannot be recognised by my software where I am trying to upload it. In Notepad I see a lot of weird signs so I'm guessing the software also cannot read it.

The message I get when opening the CSV file says:

"The file you are trying to open, 'name.csv', is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

[CK76]

... so you need CSV? You'd do it like below then. Notice the second argument added for SaveAs.

Please Login or Register  to view this content.

[Scavenger102]

Heyah

This one seems to be working

Please Login or Register  to view this content.

Many thanks for help!

[Scavenger102]

CK76 - yours is also working

Many thanks

[Scavenger102]

Hi again,

The macro above saves all the files in US format and I need it in UK format (i.e. all data with £ sign is saved in a new csv fikles with $ sign). Any tip how to fix it?

Many thanks

[CK76]

Please upload sample workbook, which replicates your issue when code is run.
Without it, it's very difficult to pinpoint the cause.

[Scavenger102]

That should now be attached

Quick explanation - The sheet "Paste Special" has two buttons assigned to macros. The one called "split sheets" saves all sheets separatelly as CSV files. But when you look at the "Data In" sheet it is oryginally provided with price in £ while the new created CSV is in $. And this is what makes the problem here.

[AlphaFrog]

I can't replicate the problem. I ran your macro in the example workbook and this is the the text from the result csv file for the Data In sheet.

Errors,,1,2,3,4,,,,,,,,,,,,,,,,,,,,
42,Price Per Test,Royal Free Pharmacy,Royal Free Pharmacy,Royal Free Pharmacy,Royal Free Pharmacy,,,,,,,,,,,,,,,,,,,,
Endotoxin Test - Water,£29.00,1,3,1,3,,,,,,,,,,,,,,,,,,,,
Endotoxin Test - Pharmaceuticals,£39.20,2,1,2,,,,,,,,,,,,,,,,,,,,,
Endotoxin Test - Renal Product ,£33.65,,,,,,,,,,,,,,,,,,,,,,,,
HTM 2030 Suite of Tests,£58.60,,,,,,,,,,,,,,,,,,,,,,,,
Total Viable Count ,£20.00,,,,,,,,,,,,,,,,,,,,,,,,
Conductivity Test,£4.80,,,,,,,,,,,,,,,,,,,,,,,,
Hardness Assay,£4.80,,,,,,,,,,,,,,,,,,,,,,,,
Heavy Metals Assay,£7.00,,,,,,,,,,,,,,,,,,,,,,,,
Total Organic Carbon Test,£0.00,,,,,,,,,,,,,,,,,,,,,,,,
Nitrates Test,£4.00,,,,,,,,,,,,,,,,,,,,,,,,
Validation of Pharmaceutical,£280.80,,,,,,,,,,,,,,,,,,,,,,,,
Full Chemistry Suite of Tests,£108.45,,,,,,,,,,,,,,,,,,,,,,,,
Period (mth - mth YR),,February 2018,February 2018,February 2018,February 2018,,,,,,,,,,,,,,,,,,,,
,,SR,SR,SR,SR,,,,,,,,,,,,,,,,,,,,
Contact Name:,,Clarissa Coker,Clarissa Coker,Clarissa Coker,Clarissa Coker,,,,,,,,,,,,,,,,,,,,
Purchase Order Number:,,RQC0201830,RQC0208832,RQC0215837,RQC0222840,,,,,,,,,,,,,,,,,,,,
Customer Code,,D276,D276,D276,D276,,,,,,,,,,,,,,,,,,,,

[CK76]

Same for me. It correctly outputs result as how it's displayed in Excel cell.