• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying Sheets to multiple output files using VBA

S

Steve DeWeese

New Member
I have a master workbook that has data for numerous locations. One sheet is a summary sheet and two additional sheets are backup detail. I am using the following VBA code that I found from another Chandoo lesson to 'break my list" into separate files for each location. That part works great but I need to add code that will also copy the supporting detail from the other two sheets in the master sheet and add them to each of the individual location workbooks. It would be ideal if the code would pull only the detail for that same location but it is fine if it copies the entire sheets to the new file. Here is what I have now:
Sub breakMyList()
' This macro takes values in the range myList
' and breaks it in to multiple lists
' and saves them to separate files.

Dim cell As Range
Dim curPath As String

curPath = ActiveWorkbook.Path & "\"

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each cell In Range("lstClinic")
[valClinic] = cell.Value
Range("myList").AdvancedFilter Action:=xlFilterCopy, _
criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False
Range(Range("Extract"), Range("Extract").End(xlDown)).Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:=curPath & cell.Value & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents
Next cell

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

The additional sheets I would like to copy are named "Surgery Details" and "Surgery Days" in the master workbook. Column A in each of these sheets has the name of the location. Row 1 has a header and the data begins in row 2.

Any help would be greatly appreciated.
 
Here is a sample version. I took most of the data out so it was small enough to upload. There are over a thousand rows on each of the detail worksheets.

Thank you.
 

Attachments

  • Deliver Report_Nov_2013_Sample.xlsm
    28.1 KB · Views: 6
Where do you want the additional data in the new file?

In your data does the Clinic on the 2nd and 3rd Sheet occur more than once?
 
Last edited:
If it would also be possible to add an email feature, it would be great to automatically distribute the files to the manager at each location. I could add another worksheet with a table that has the location and email address.

Thanks in advance for your help.
 
kchiba said:
Where do you want the additional data in the new file?

In your data does the Clinic on the 2nd and 3rd Sheet occur more than once?
Click to expand...
The additional data should go on a 2nd and 3rd sheet in the new file so that the individual files resemble the master. The first worksheet would have that locations data from the summary of the master. The second sheet would have the surgery details for that location and the third would have the surgery days for that location. Each location will have multiple entries (data in rows), I just stripped that out to cut down the file size and only left one entry for each location as an example.
 
Hi Steve,

This is going to take some time, I am trying to get your existing VBA to work first and will then add the other bits.

I am not sure if I can help you with the email side, but lets see waht we come up with

cheers

kanti
 
Hi steve,

Please fix up the upload file, there are a number of differences in column headers, and the clean up is tkaing too much time
 
My apologies, I should have tested it before uploading. It was working on the regular file but there was data where the header should have been blank on the "sample version". This file now works for splitting the master, I just need to add the script to include the other two worksheets when the files split.

Thank you for your time.
 

Attachments

  • Deliver Report_Nov_2013_Sample_1.xlsm
    28.7 KB · Views: 4
Hi Steve,

Please test ou the attached, I have not done the email bit, I will leave that to you as you can refer to the link kindly provided by SIRJb7.

You will note that I have changed a number of your Named Ranges

cheers
kanti
 

Attachments

  • Deliver Report_Nov_2013_Sample_2.xlsm
    35.1 KB · Views: 6
This works fantastic! Thank you!

Is it possible to maintain the formatting when the data is pasted in the new workbooks? It is pasting with a light blue fill and the column widths are not the same as the master. I truly appreciate this help, this will save me a great deal of time.
 
The link for the email code is great. I need a little help customizing it for this application. I inserted the code below before the wbTar.Close command in the workbook hoping that it would treat that file as the Activeworkbook and repeat for each individual file. I used my email address for the test. It did run without errors (once I changed to 'sub and 'end sub) but it emailed the master workbook to me instead of each individual file. I would also like to be able to use an email address from a table that matches each location. I have added a table that has the address for the manager at each location to the last worksheet. Please see the revised file
 

Attachments

  • Deliver Report_Nov_2013_Sample_3.xlsm
    34.3 KB · Views: 3
Hi, Steve DeWeese!
Not having analyzed your code yet, the reason is this line:
.Attachments.Add ActiveWorkbook.FullName
That's why it's sending the whole actual workbook attached.
If you tell us which is the file you want to attach to the mail (cell, variable, where?), it'd be helpful.
Regards!
 
SirJB7 said:
Hi, Steve DeWeese!
Not having analyzed your code yet, the reason is this line:
.Attachments.Add ActiveWorkbook.FullName
That's why it's sending the whole actual workbook attached.
If you tell us which is the file you want to attach to the mail (cell, variable, where?), it'd be helpful.
Regards!
Click to expand...
[valClinic] = cell.Value
wbTar.SaveAs Filename:=curPath & cell.Value & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

valClinic is a named range that points to cell A2 on the Delivery Report worksheet.
 
Hi, Steve DeWeese!
Have you tried using [valClinic].Value instead of ActiveWorkbook.FullName?
Maybe you have to add something like:
ActiveWorkbook.Path & Application.PathSeparator & [valClinic].Value
or the equivalent:
curPath & Application.PathSeparator & [valClinic].Value
Regards!
 
I will give that a try. I'm brand new to VB, so I wasn't sure how to designate the file so it would line up with the one being created in the scrip from kchiba.

Thank you,

Steve
 
Steve DeWeese said:
I will give that a try. I'm brand new to VB, so I wasn't sure how to designate the file so it would line up with the one being created in the scrip from kchiba.

Thank you,

Steve
Click to expand...
Unfortunately, that didn't work. The other script is also adding the date and time to the file name. Is it possible that is preventing it from recognizing / finding the the file? The script runs but nothing is attached to the emails.
 
Hi Steve,

I have not tested the code, but have made an important change,
.Attachments.Add ActiveWorkbook.FullName
.Attachments.Add wbTar.FullName

wbTar is the workbook that has been created, so no it will attach that one.

please test and let me know if it is OK

cheers

kanti
 

Attachments

  • Deliver Report_Nov_2013_Sample_4.xlsm
    34.5 KB · Views: 8
This works great. The only thing I need to figure out now is how to pull the email address for each of these files from a list of email addresses. In reading one of the examples from SirJB7, I see code that will generate emails and attach files based on the email addresses and file names in a table. If anyone can help me to populate fields in sheet1 with the file names as they are created, or if there is any easier way to populate the email ".to =" code with a lookup of the email address for the current location I will have everything I need.

Everyone's help is greatly appreciated.
 
I was wrong....when I tested it out this morning, I only paid attention to the code attaching the files to individual location files and prior to that, I was checking for the additional worksheets being added to each file. I failed to notice that the information that is output to each file for the delivery report sheet is no longer correct. Somewhere along the way, one of the changes now has the information from the surgery day worksheet output to the delivery tab instead of the information that should be output to the delivery tab. This tab should split out a single row of information from the master workbook that has the data for that location. The code in Sample 2 is working correctly except that I would like it to default to the delivery report sheet as the first sheet that opens in the split files. Right now it is opening to the surgery day worksheet, which is the last one pasted.

I'm going to try and patch this together myself but my limited ability to understand what the code is doing is making this very challenging.

Thank you,

Steve
 
Hi, Steve DeWeese!
Won't be back till Monday, late at night I guess. Will come back here to check how it went on.
Regards!
 
Hi Steve,

Been away, what is the status on this thread, is it working, if not where do you stand with the problem?

kanti
 
You must log in or register to reply here.
Back
Top