Save each sheet in array as New workbook to separate files

[Tim Hanson]

Hello,

Edit
I did get it to work I updated my code.
Still have small issue, how do you move up out of a folder
tiring

MyFilePath & "\" & "\..\" & SheetName & ".csv"

but not working

Thanks

Sub SaveNodeAsBook()
    Dim SheetName As String
    Dim MyFilePath As String
    Dim fileName As String
    Dim ws As Worksheet
  
    MyFilePath = ThisWorkbook.Path & "\RawDataLinkedIn"
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    For Each ws In Worksheets(Array("GephiNodeFile", "GephiEdgeFile"))
    SheetName = ws.Name
    ws.copy
    
    With ActiveWorkbook
        '~save book in this folder
        ActiveWorkbook.SaveAs fileName:=MyFilePath & "\" & "\..\" & SheetName & ".csv"
        ActiveWorkbook.Close SaveChanges:=True
    End With
            
    '~Delete Sheet
   ' Sheets("SheetName").Select
   ' ActiveWindow.SelectedSheets.Delete
            
            
    Next ws
    'Sheet1.Activate
End Sub

 

[AIM]

Hi Tim,

Try this:

ActiveWorkbook.SaveAs fileName:=MyFilePath & "\..\" & SheetName & ".csv"

Regards!!

 

[Tim Hanson]

AIM, thanks, when I add your code I get "Script out of range" and

 For Each ws In Worksheets(Array("GephiNodeFile", "GephiEdgeFile"))

is highlighted.

 

[AIM]

Hi,

Can you upload a sample workbook, as I am not getting any errors while running the code and the files are being created on my desktop.

Regards!!

 

[Tim Hanson]

AIM it took me awhile to remove sensitive data and remove most of the macros, here is the folder with all the sub folders, the Sub SaveNodeAsBook() saves the files to the folder RawDataLinkedIn not the GephiNodeFile and GephiEdgeFile

Thank you

 

[Tim Hanson]

AIM, I figured out, thanks again

 

[Tim Hanson]

I forgot to post the working code, which looks like it should not work since

fileName:=MyFilePath & "\" & SheetName & "_" & Format(Now(), "DD-MM-YY hh.mm") & ".csv"

and

MyFilePath = ThisWorkbook.Path & "\" & SheetName

Both have SheetName in them

Sub SaveSheetsAsNewBooks()
Dim SheetName As String
Dim MyFilePath As String
Dim fileName As String
Dim ws As Worksheet


With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

For Each ws In Worksheets(Array("GephiNodeFile", "GephiEdgeFile"))
SheetName = ws.Name
ws.copy

MyFilePath = ThisWorkbook.Path & "\" & SheetName

With ActiveWorkbook
'~save book in this folder
ActiveWorkbook.SaveAs fileName:=MyFilePath & "\" & SheetName & "_" & Format(Now(), "DD-MM-YY hh.mm") & ".csv"
ActiveWorkbook.Close SaveChanges:=True

'~Delete Sheet
Sheets(SheetName).Select
ActiveWindow.SelectedSheets.Delete

End With

Next ws

End Sub
[/CODE]