most efficient wat to QUICKLY paste values to another book.

[moonbreakker]

Hello,

For past couple of months, i am learning VBA basics and i figured that often there are more than a couple of ways for getting same results.

I have few codes from my old workbooks, some are pathetically slow at pasting and some are very efficient.

I have attached a workbook with 2 sheets. "Master" sheet contains clinical data of patients. 1 row per patient and "clinical" sheet is how report of the patient is generated on an A4 size page.

I am trying to do the following:

1. Make a copy of sheet "clinical" at path D:\xyz\xyz\1.xlsx --- 1 being Sr No, serial number of the row as in column A.
2. Copy (paste special values) contents from master sheet row 1, to 1.xlsx created.
3. Save file ---> Close.
4. Create 2.xlsx to paste values from row 2 "master". ---> Save close, 3.xlsx and so on..

So if i have 500 rows, i will have 500 individual workbooks of patients in a folder named as 1.xlsx,2.xlsx,3.xlsx...and so on..

--> I want to paste special VALUES only.
--> Also paste if some cells in a row are empty.
--> Overwrite if destination file exists.

In code i use, macro always runs from beginning and recreates all 500 files again. For eg, if i create a new row 501 for new patient and i want to generate only 501.xlsx, my code runs from beginning and creates 500 existing files again. Anyway to paste only selected rows? I can select Sr Nos that are contiguous and macro will only create workbooks for those selected Sr Nos.

Please Note: In sheet clinical i have already created a table with cell reference. (Where to paste what)

Some of the slow and efficient codes i was using earlier for other reports are as follows:

Slow: It's long i have just posted part of the code.

Please Login or Register  to view this content.

Code 2 which i use for another report. (Faster and shorter)

Please Login or Register  to view this content.

Code 3.

Please Login or Register  to view this content.

All of the above codes do the same job. I don't know how to set the references for my new attached workbook.
Can anyone help me write codes for attached book?

Thank you.

[LeoTaxi]

For Selected Row, so only 1 new file

Please Login or Register  to view this content.

For All

Please Login or Register  to view this content.

Kind regards
Leo

[moonbreakker]

Thanks Leo,

Does'nt run. Debug gives error at - Sheets(Sheets.Count).Name = ws1.Range("D" & x)

I have attached the file.

[LeoTaxi]

It does run

remove data after row 10


Cheers
Leo