Modify existing code to work with new workbook.

**moonbreakker** · 09-10-2016, 01:07 AM

Hello,

I am new to VBA and can manage basics like change path and file names. I have attached 2 workbooks and a code below.
What code does is:

1. Make copy of AUDIOGRAMS.xlsx and number it 1.xlsx.
2. Copy data from AUDIOGRAM.xlsm and paste it to 1.xlsx to generate a chart.
3. Save workbook --> Close workbook.
4. Create 2.xlsx for next row in audiogram.xlsm.

So each row in audiogram.xlsm is used to generate charts for individual patients.

PLEASE NOTE: The code below won't work for the attached sheets. This code works with other sheet which is used to generate other workbooks for blood reports. But the concept is same.

I need to copy 43 values in a row ( Ref By EMP NO NAME DEPT. AGE *** DATE LEFT EARFT AIR 250 LEFT EARFT AIR 500 LEFT EARFT AIR 1K LEFT EARFT AIR 1.5K LEFT EARFT AIR 2K LEFT EARFT AIR 3K LEFT EARFT AIR 4K LEFT EARFT AIR 6K LEFT EARFT AIR 8K LEFT EARFT BONE 250 LEFT EARFT BONE 500 LEFT EARFT BONE 1K LEFT EARFT BONE 1.5K LEFT EARFT BONE 2K LEFT EARFT BONE 3K LEFT EARFT BONE 4K LEFT EARFT BONE 6K REMARKS LEFT EAR RIGHT AIR 250 RIGHT AIR 500 RIGHT AIR 1K RIGHT AIR 1.5K RIGHT AIR 2K RIGHT AIR 3K RIGHT AIR 4K RIGHT AIR 6K RIGHT AIR 8K RIGHT BONE 250 RIGHT BONE 500 RIGHT BONE 1K RIGHT BONE 1.5K RIGHT BONE 2K RIGHT BONE 3K RIGHT BONE 4K RIGHT BONE 6K REMARKS RIGHT EAR )

and paste it to sample woorkbook's copy created. (1.xlsx) For eg. I2 to Q2 in sample data file to M6 to M14 in sample file. Similarly for name, age , date , remarks etc. B2 in data file to AR2 in data file (43 entries) need to be copied to sample file to create charts.

I don't understand how the reference cells are set in code below.

These lines decide where to paste data i guess.

wsCard.Cells(6, 3).Offset((card - 1) * 9) = wsSource.Cells(i, j).Offset(, 1)
wsCard.Cells(7, 3).Offset((card - 1) * 9) = wsSource.Cells(i, j).Offset(, 2)
wsCard.Cells(6, 7).Offset((card - 1) * 9) = wsSource.Cells(i, j).Offset(, 3)
wsCard.Cells(7, 7).Offset((card - 1) * 9) = wsSource.Cells(i, j).Offset(, 4)
wsCard.Cells(8, 4).Offset((card - 1) * 9) = wsSource.Cells(i, j).Offset(, 5)
wsCard.Cells(9, 3).Offset((card - 1) * 9) = wsSource.Cells(i, j).Offset(, 6)

Thank you.

If code is misleading, please avoid the code. Simply put -

I want to copy 43 entries in a row to sample blank file, save file and create new file If there are 100 rows, 100 new workbooks will be created in the folder named 1.xlsx, 2.xlsx, 3.xlsx and so on. Only first column Sr No is not copied. Rest 43 need to get copied to their corresponding places.

Please Login or Register  to view this content.

**LeoTaxi** · 09-10-2016, 07:30 AM

Please Login or Register  to view this content.

Kind regards
Leo

**moonbreakker** · 09-10-2016, 02:18 PM

Hello, Thank you for replying and i really appriciate the time you gave for the above code.

Sadly, this does not work. Your code seems to create new files instead of making "copies" of my sample file. My sample file has charts ready with lines and axis and name and *** and all. I thought that just pasting data would modify chart and then we can save it as 1.xlsx and so on with different patients.

In your code files get created without the chart. I dont want chart to be designed. Its already designed. So we just need to make copy of workbook and feed in new data of new patient.

Anyways, just hold on as of now, i am looking for another code online. I will get back to you soon.

Thanks again.

**LeoTaxi** · 09-10-2016, 08:39 PM

With exemple graph in thisworkbook

Please Login or Register  to view this content.

Kind regards
Leo

**moonbreakker** · 09-12-2016, 10:05 AM

Hello Leo

Code works great!! Thank you. I added a couple of more entries in my example file and your code file. Some of the references have changed.
I don't how to set arr(etc etc).

I have added Specific history C9, interpretation D43, Recommendation D49.
D45 and D47 are Left and Right ear remarks. Please check new files.

I am attaching the final files. Can you please make your code work with the new files attached?

Thank you for your time.

**moonbreakker** · 09-12-2016, 10:14 AM

Leo, one more thing.

100s of files get created in the folder. I used to print by selecting 15 workbooks at a time and SHIFT+Print. (Windows allows max 15 books at a time.) Thats ok.

Problem is i want to print only page 1/2 and not the data part. Any suggestions? Its ok even if there is no data in the files created, but i don't think thats possible as graphs won't be generated. Correct?

Thanks.

**LeoTaxi** · 09-12-2016, 04:54 PM

for this 12 K insert 2 columns between left and right ?
and 2 more ad end ?

Kind regards
Leo

**moonbreakker** · 09-13-2016, 04:39 AM

Leo,

3 more entries will be pasted in sample file which are at the end. They will be pasted at:

I have added Specific history C9, interpretation D43, Recommendation D49.

Also location of comments for left ear and right ear has changed. New location is: D45 and D47 are Left and Right ear remarks.

**moonbreakker** · 09-13-2016, 04:45 AM

OOps!

Sorry, 2 new entries. In your file at the end. In my example file at Specific history C9 and Recommendation D49.

Please find new sample file and your file attached.

Thank you for this. You have been of great help.

**LeoTaxi** · 09-13-2016, 05:19 PM

I come to this then

Please Login or Register  to view this content.

Kind regards
Leo

**moonbreakker** · 09-14-2016, 01:01 AM

Hey Leo, Thanks. This one is working perfect.
It starts printing on its own. I tried to remove

With ActiveSheet
.Columns("A:J").PrintOut
.Move
End With

from the code but then it gives some error. I just want to save all workbooks in a folder first. I print them later on color printer from another machine.
I will look for another code, maybe outside excel like batch file or something to print only 1/2 pages in that folder. Which lines i should remove from your code to avoid printing?

Thank you.

PS: This one is working perfect, please don't remove anything except the printing part. Thanks again! You made my day.

**moonbreakker** · 09-14-2016, 01:03 AM

Also if file 1,2.xlsx etc. exists in destination, it asks if i want to replace it. I want to overwrite existing files without prompt.

**LeoTaxi** · 09-14-2016, 02:59 AM

Removed this, was only part from next post

**LeoTaxi** · 09-14-2016, 03:53 AM

without printing and prompt

Please Login or Register  to view this content.

and seperate code for printing

Please Login or Register  to view this content.

Cheers
Leo

**moonbreakker** · 09-14-2016, 10:29 AM

Thanks Leo. Everything is not working perfect as required. Closing the thread. Thanks again!

**LeoTaxi** · 09-14-2016, 12:03 PM

So, wich part is not working ?

Modify existing code to work with new workbook.

LinkBack

Thread Tools

Rate This Thread

Display

Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Re: Modify existing code to work with new workbook.

Thread Information

Users Browsing this Thread

Similar Threads

Modify Existing VBA Code

Need to modify Existing Code

[SOLVED] Modify existing Code.

[SOLVED] Help modify existing Copy-Paste code

[SOLVED] Modify an existing macro to not copy to the same workbook but another one.

[SOLVED] Modify Existing Mbr Code to Save to Ext.Workbook

Bookmarks

Bookmarks

Posting Permissions