Macro to save sheets into separated workbooks

[jalrs]

Hello guys,

What im looking for here is to save sheets into different workbooks, where workbook name = sheet name. After doing my research i came to this bit of code, but, unfortunately it only copies the first sheet where the name is <> than "Readme" as code shows.

My code:

Sub CriarWBs()

totalsheets = ThisWorkbook.Sheets.Count

For i = 1 To totalsheets

strsheetname = ThisWorkbook.Sheets(i).Name

If strsheetname <> "Readme" Then

Sheets(strsheetname).Select
Sheets(strsheetname).Copy
ActiveWorkbook.SaveAs Filename:="C:\Users\joafrodrigue\Desktop\teste\Difusao\" & strsheetname & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close

End If

Next

End Sub

Thank you very much guys!

 

[jalrs]

When I tested the macro it inserted the formula in AY. When I entered a value in AX, the value in AY was returned correctly. What does the formula in AY look like after you run the macro?

So i retested, and i noted another thing:

It works if i generate new workbooks without performing the first block of columns on readmeWS on templateWB

The first column of blocks filters the information from the masterdataWB, on demo, reprsented as copy.....xlsm, according to each department and sends them to each worksheet according to which button we choose to press, for the context.

So problem now resides on, my end user only works with AX as AY gets autofilled according to AX, but they need to know which product, serie number etc they are working on, so my job as 2nd last end user is to click on the first column block buttons and filter the information, and generate the new WBs with the data filled in, so they can work only on AX. Note that my job as 2nd last end user is already done, its coded already and you can check the code if you think the problem might be there. its represented as each department module on copy.xlsm WB

thanks

 

[mumps]

click on the first column block buttons and filter the information, and generate the new WBs with the data filled in

The macro I suggested creates new workbooks for all the teams. Do you want to use these buttons to create a new workbook for only one particular team after the filtered data has been copied to it?

 

[jalrs]

The macro I suggested creates new workbooks for all the teams. Do you want to use these buttons to create a new workbook for only one particular team after the filtered data has been copied to it?

The macro you suggested creates new workbooks for all the teams and that is correct.

By steps: (Source->Destination)

1st step - Click on each button and get the filtered data into each worksheet (first block on readmeWS on TemplateWB) - in order to achieve this data, my copyWB has to be open, otherwise it fails to execute. Note that on the copyWB all the data is merged, thats why there's this need of macro filtering it into new workbook within new worksheets for each department/team. CopyWB ->TemplateWB

2nd step is to create each team workbook with the filtered data in, except for columns AX AY AZ BA BB BC - TemplateWB -> IndividualWBs

3rd step is to send each new workbook with the filtered data to each department/team - TemplateWB (readmeWS block on the right performs this action)

4th step is end user related, but i must grant its operational, and its that they choose one option within the ones they have on column AX and that option returnes a value on AY, according to TAB_FDB sheet table- IndividualWBs

5th step is end user related too, and its not on my side, they have to save the file into a specific folder, but theyll do it manually, so i dont have to worry about it.

So i described the context of the case here, let me know if i wasnt clear enough.

Not sure if you read it or not, but i retested and described on #21

Thank You Mumps

 

[mumps]

Instead of having a separate button to get the filtered data for each worksheet, would it work for you to have one button to do all the worksheets at once?
Do you also want one button to send all the emails instead using separate buttons?

the filtered data in, except for columns AX AY AZ BA BB BC

Do you not want to copy the data in these columns. In other words, do you want these columns to be blank in the individual sheets?

 

[jalrs]

Regarding 1st question: It would work if the output is the same as it is now. Unfortunately i did not know how to perform it back when i was searching for the code i ended up using.

Regarding 2nd question: Can also be, again unfortunately, did not know how to perform it, since each department has different .to, .ccs, .bccs. The table you see in readmeWS where i grab the cell values, they are the same there, but when i get the project validated, they will be different between areas

Regarding 3rd question: Yes, those columns must be blank in individual WS, and consequently, in individualWBs when they are generated. They are supposed to be filled by end users (users that receive the emails). and they will fill the column AX and then get a value returned on AY according to TAB_FDB WS table. AZ BA BB BC are free text columns.

TAB_FDB worksheet will have more tables there, i just need to know how does the vlookup work within a vba macro, so i can apply each future table to each area. The table there is relevant to Porto team for example.

Let me know if i wasnt clear

1st and 2nd arent mandatory, of course. i see it working, its personal preference i believe

Thank You Mumps once again

 

[mumps]

OK. Give me a little time to work on this.

 

[jalrs]

OK. Give me a little time to work on this.

sure, take your time and thank you very much for your time assisting me!

 

[jalrs]

OK. Give me a little time to work on this.

Mumps! Sorry, big mistake from me.

dont think 1st will work. as you can see on "porto" module, i have 2 criterias for filtering, first being "porto", 2nd being "em tratamento", on
others i only have one, because i havent been instructed to the other criterias for each, yet. i know all are "em tratamento" on that demo, but it's just a demo, i was going to adapt it when i got the other criterias from my boss

so id say, better to keep 1st as it is, 2nd should be alright tho. sorry, just remembered it now.

critical point is the vlookup thing tho

 

[mumps]

Will there always be two criteria for each one? Can you get a complete list of the criteria from your boss?

 

[jalrs]

Will there always be two criteria for each one? Can you get a complete list of the criteria from your boss?

i have a meeting tomorrow, i can ask, im from portugal (+5). but if thats a big deal, you can just leave the code regarding 1st question like i left coded and i can adapt it when i get the list of criteria.

my main focus here is indeed the vlookup question, need to get it sorted.

thanks again mumps