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]

The link did not include the Template File. The current Template file I have has sheet names such as Porto, Sporting, Benfica, etc. I need the Template that has the sheet names Apoio SP, Armazem, etc.

My apologies. i splited template2 file into each wb template as suggested on 2nd method. you can find them inside demoworkbook folder. each worksheet on template2 xlsm is now a workbook.

for loja.xlsx the template there already has concerned the fact that it has less columns than the other templates due to needing less columns from Stock Trânsito @copyJOAOxlsm

and readmews on template2.xlsm is now on copyJOAOANALISE.xlsm

this looks easier to perform, but in case you need, i can sort a new template.xlsm and upload

let me know if anything was not clear

thanks mumps

 

[mumps]

It's difficult to help when things keep changing. The original idea was that the macro first copies the data from the "Copy of COPYJoao_AnaliseST2021.xlsm" file to the sheets in the Template file. Then it creates each individual workbook from the sheets in the Template file, saves them and then emails them. If now we have to deal with individual workbooks instead of the sheets in Template file, I would have to start all over again from the beginning. To be honest, I don't want to start all over again. If we can't proceed with the process I described in red, then I'm afraid that I won't be able to help any further. If you want to proceed as I have described, then please upload the Template that has the sheet names Apoio SP, Armazem, etc.

 

[jalrs]

It's difficult to help when things keep changing. The original idea was that the macro first copies the data from the "Copy of COPYJoao_AnaliseST2021.xlsm" file to the sheets in the Template file. Then it creates each individual workbook from the sheets in the Template file, saves them and then emails them. If now we have to deal with individual workbooks instead of the sheets in Template file, I would have to start all over again from the beginning. To be honest, I don't want to start all over again. If we can't proceed with the process I described in red, then I'm afraid that I won't be able to help any further. If you want to proceed as I have described, then please upload the Template that has the sheet names Apoio SP, Armazem, etc.

Yes mumps, i will do it. My apologies again. My intention was not to be changing scenarios all over the work. Its not something i can control, but im deeply sorry mumps.

As said, i will update the Template file and reupload it. i thought the way i provided you hours ago would make it easier to reach the finish line. at least it looked like.

ill comeback shortly.

 

[jalrs]

It's difficult to help when things keep changing. The original idea was that the macro first copies the data from the "Copy of COPYJoao_AnaliseST2021.xlsm" file to the sheets in the Template file. Then it creates each individual workbook from the sheets in the Template file, saves them and then emails them. If now we have to deal with individual workbooks instead of the sheets in Template file, I would have to start all over again from the beginning. To be honest, I don't want to start all over again. If we can't proceed with the process I described in red, then I'm afraid that I won't be able to help any further. If you want to proceed as I have described, then please upload the Template that has the sheet names Apoio SP, Armazem, etc.

NEWDEMO2 there it is

thank you mumps for your patience with me

 

[mumps]

Please note the following problems:

copy all data from columns A to AV and also column BH

If we do this, column BH becomes column AY so the formulae are entered in what used to be column BH.

Loja department, where we only want to copy range A:T + W:AC + AF:AV + BH column

This creates a similar problem as the first.

 

[jalrs]

Please note the following problems:

If we do this, column BH becomes column AY so the formulae are entered in what used to be column BH.


This creates a similar problem as the first.

Hey Mumps, thanks for keeping up with me!

Tomorrow when i turn on the company laptop ill see what you are refering to and ill get back to you.

Have a good night!

 

[jalrs]

Please note the following problems:

If we do this, column BH becomes column AY so the formulae are entered in what used to be column BH.


This creates a similar problem as the first.

Sorry, i don't follow the problems mentioned as:

Column BH becomes column AW on 1st case: "Recuperação de Pendentes"
On 2nd case, column BH becomes column AS.

Regarding vlookup formula, it would be located on column AY for 1st case, and column AU for 2nd case.

Thanks

 

[mumps]

I think that your case is becoming a little too complicated for me with too many changes and variables. Perhaps another member will be able to help.

 

[jalrs]

I think that your case is becoming a little too complicated for me with too many changes and variables. Perhaps another member will be able to help.

is it bad to create a new thread were i fully detail what i am missing?

i mean, i'm deeply grateful for your help, if you don't see a way for this to work, i understand. i'm just trying to finish my bsc that has nothing to do with vba. thats why i'm new to it, been forced to learn it, but with 3 projects for 3 months, i'm not gonna get out of it mastering it. thats why i seek help here.

i also firmly believe that the finish line was quite close, the code was working. my boss just said "that works yes, but imagine if we have a new department. the code won't work for that department unless someone manually adds a new module and copy pastes other modules and adapts it ot the new department. "

he's right, thats why i thought about an easy way out for it, where i presented you my vision on 2nd method for N departments.

Sorry for the long text, felt like saying it

Thanks Mumps

 

[mumps]

Good luck with it. Please keep in mind that to get a working solution, you need to have consistency in how the data should be organized. Too many variables, especially if they change from time to time, make it very hard to code.