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!

 

[mumps]

Try:

Sub CriarWBs()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Readme" Then
            Sheets(ws.Name).Copy
            With ActiveWorkbook
                .SaveAs Filename:="C:\Users\joafrodrigue\Desktop\teste\Difusao\" & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
                .Close False
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[jalrs]

Try:

Sub CriarWBs()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "Readme" Then
            Sheets(ws.Name).Copy
            With ActiveWorkbook
                .SaveAs Filename:="C:\Users\joafrodrigue\Desktop\teste\Difusao\" & ws.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
                .Close False
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

it indeed works. i actually found out why it was buggy for me, i had two hidden sheets.

Now when unhiding the sheets, both sheets also are copied. how to prevent this? i know it's somewhere on the if clause, i tried:

if ws.name <> "Readme" or ws.name <> "TAB_FDB" or ws.name <> "Resumo" then

But it didnt work, any help on this?

Thank you so much!

EDIT: Problem appeared, when i close and re-open the book, it shows (4), and when i click enable macros, the sheets simply don't appear (5). see attachments pls

 

[jalrs]

it indeed works. i actually found out why it was buggy for me, i had two hidden sheets.

Now when unhiding the sheets, both sheets also are copied. how to prevent this? i know it's somewhere on the if clause, i tried:

if ws.name <> "Readme" or ws.name <> "TAB_FDB" or ws.name <> "Resumo" then

But it didnt work, any help on this?

Thank you so much!

EDIT: Problem appeared, when i close and re-open the book, it shows (4), and when i click enable macros, the sheets simply don't appear (5). see attachments pls

I found solution for the edit part. would appreciate any help regarding the question before edit

Thanks again

 

[mumps]

Try:

If ws.Name <> "Readme" And ws.Name <> "TAB_FDB" And ws.Name <> "Resumo" Then

 

[jalrs]

Works great, thank you very much!

Try:

If ws.Name <> "Readme" And ws.Name <> "TAB_FDB" And ws.Name <> "Resumo" Then

 

[mumps]

You are very welcome.

 

[jalrs]

hey mumps, sorry to bother again!

was doing some research and i realized i need to bring another sheet with me, is there an easy way to do this? i mean, bring 2 sheets into a new workbook?

i looked here on mrexcel, and found this thread: Importing Multiple Workbooks that have multiple worksheets where you actually give it a go, but it's quite different from what i want.

also looked some tutorials but the ones i saw use arrays and its hard for me to explain myself using arrays.

if arrays is the only way to go, ill look through it. just trying my luck if you have an easier method.

thank you again!

 

[jalrs]

You are very welcome.

so what i'm needing now is: i have one sheet called "readme", one sheet called "resumo", one sheet called "armazém" and one sheet called "tab_fdb"

what im asking above if it wasnt clear, is to create a new workbook with "armazém" and "tab_fdb" there. i only need for one department, the others ill adapt myself

thanks

 

[jalrs]

so what i'm needing now is: i have one sheet called "readme", one sheet called "resumo", one sheet called "armazém" and one sheet called "tab_fdb"

what im asking above if it wasnt clear, is to create a new workbook with "armazém" and "tab_fdb" there. i only need for one department, the others ill adapt myself

thanks

other way of doing this is to just set the table on the "armazém" sheet.

for context, tab_fdb is a table where i navigate through with with vlookup and use it to autofill columns.

i tried to just copy and paste the table into the sheet im applying it, but when it generates the new workbook, the column where i have the vlookup assigned doesnt grab the formula nor the table grabs its values. what i got every single time was an empty table