VBA_SaveAS_1st 4 worksheet as csv file

[Nyay]

Hi All,

I need help with regards to Saving my present workbook. So basically I want to use SaveAs which will be in Module and attach the module into Foam Control.

But I want to SaveAs only first 4 sheet, in 5th Sheet I have put the button for ease. Also in the 4 sheet I only want data till the last column and last row and not the tab which I have created to put the Month name in a cell. I am attaching the file please help me with SaveAs, 1st 4 worksheet data till last column and row into csv.

[jdelano]

Try changing your cmdSave code to

EDIT: updated the code due to the SaveAs changing the sheet names in the

    Dim csvFileName As String
    Dim ws As Worksheet
    Dim originalSheetName As String
    Dim originalWBName As String
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    originalWBName = ThisWorkbook.FullName ' the export changed the name of the workbook - save it here
        
    ' loop through each sheet and export them as csv files
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "SaveAsButton" Then
            ' export the ws if it isn't the one with the button
            originalSheetName = ws.Name
            csvFileName = ThisWorkbook.Path & "\" & ws.Name & VBA.Format(VBA.Now, " dd-MMM-yyyy hh-mm") & ".csv"
        
            ws.SaveAs Filename:=csvFileName, FileFormat:=xlCSV, CreateBackup:=False
            
            ' using worksheet.SaveAs changes the sheet name to the file it was saved as
            ' put it back to the name it was beforehand
            ws.Name = originalSheetName
        End If
    Next ws

    ThisWorkbook.SaveAs originalWBName, xlOpenXMLWorkbookMacroEnabled   ' and make sure the workbook stays the same name
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

[Nyay]

Hi

Attachment 872501

I removed the sheet 5 and changed put the SaveAs Tab in Sheet 4, now when I click the button the sheet 4 name changes to the SaveAs Workbook name. I don't want to change the worksheet names, also my workbook name Report constant just the month name keep on changing, Report_July, Report_June,Report_May. Also I have to SaveAs the file into the destination folder.

Sub cmdSave2()
    Dim csvFileName As String
    Dim ws As Worksheet
    Dim currentMonth As Long
    Dim DestinationPath As String
    
    
    
    Application.DisplayAlerts = False
    
    ' loop through each sheet and export them as csv files
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "SaveAsButton" Then
            ' export the ws if it isn't the one with the button
            csvFileName = "Report" & currentMonth & ".csv"
             
            DestinationPath = "D:\Downloads\"
            ws.Saveas DestinationPath & FileName:= csvFileName, FileFormat:=xlCSV, CreateBackup:=False
        End If
    Next ws

    Application.DisplayAlerts = True

End Sub

Screenshot 2024-06-20 141015.png

[jdelano]

See my edit above

[Nyay]

I am sorry, I guess I confused you,

So I require all the 4 sheet into the same workbook only, I don't want that each sheet to be saveas into different CSV file.
All the Worksheet in the same WorkBook as SaveAs, only the column and till last row data populated in each worksheet should be saved into the destination Folder and the file name should be e.g. - Report_June for June Month report and when in July when I do the saveas by making some changes it should be Report_July in the month of July only.

[jdelano]

Oh, well yeah, there is nothing in the first post indicating the save as csv is date driven.

To be clear, if the current month (today it is June) then only save the June sheet as a csv file?
If that is the case, then you'll need to modify the code to be something like:

Sub cmdSave2()
    Dim csvFileName As String
    Dim ws As Worksheet
    Dim currentMonth As Long
    Dim DestinationPath As String
    Dim originalSheetName As String
    Dim originalWBName As String
    
    Application.DisplayAlerts = False
    currentMonth = MonthName(Month(Date))
    DestinationPath = "D:\Downloads\"

    originalWBName = ThisWorkbook.FullName ' the export changed the name of the workbook - save it here

    ' loop through each sheet and export them as csv files
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "SaveAsButton" and ws.Name = currentMonth Then
            ' export the ws if it isn't the one with the button and its name matches this month name
            originalSheetName = ws.Name
            csvFileName = "Report_" & currentMonth & ".csv"
             
            
            ws.Saveas DestinationPath & FileName:= csvFileName, FileFormat:=xlCSV, CreateBackup:=False

            ' using worksheet.SaveAs changes the sheet name to the file it was saved as
            ' put it back to the name it was beforehand
            ws.Name = originalSheetName

        End If
    Next ws

    ThisWorkbook.SaveAs originalWBName, xlOpenXMLWorkbookMacroEnabled   ' and make sure the workbook stays the same name
    Application.DisplayAlerts = True

End Sub

If you don't want to export the entire worksheet, then you'll need to set a range to export out.

[Nyay]

Attachment 872505

I am getting debug error.

Screenshot 2024-06-20 162532.png

[jdelano]

that is a string. change the Dim statement

[Nyay]

Sub cmdSave2()
    Dim csvFileName As String
    Dim ws As Worksheet
    Dim currentMonth As String
    Dim DestinationPath As String
    Dim originalSheetName As String
    Dim originalWBName As String
    
    Application.DisplayAlerts = False
    currentMonth = MonthName(Month(Date))
    DestinationPath = "D:\Downloads"

    originalWBName = ThisWorkbook.FullName ' the export changed the name of the workbook - save it here

    ' loop through each sheet and export them as csv files
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "SaveAsButton" And ws.Name = currentMonth Then
            ' export the ws if it isn't the one with the button and its name matches this month name
            originalSheetName = ws.Name
            csvFileName = "Report_" & currentMonth & ".csv"
             
            
             ws.Saveas DestinationPath & FileName = csvFileName, FileFormat:=xlCSV, CreateBackup:=False

            ' using worksheet.SaveAs changes the sheet name to the file it was saved as
            ' put it back to the name it was beforehand
            ws.Name = originalSheetName

        End If
    Next ws

    ThisWorkbook.Saveas originalWBName, xlOpenXMLWorkbookMacroEnabled   ' and make sure the workbook stays the same name
    Application.DisplayAlerts = True

End Sub

I corrected the code but still it is not functioning properly

The file which is Saved the WorkBook and Worksheet Name is False and only Sheet 1 from the original file is saved

I have attached the file which is Saved post running the above code.

[jdelano]

When you stated this "destination Folder and the file name should be e.g. - Report_June for June Month report and when in July " it sounded like you only wanted the current month exported. If that isn't the case then remove the check of the currentMonth against the sheet name

[Nyay]

I guess I confused you, I am sorry

So basically what I want is that I want to SaveAs my current workbook with Name Report_MonthName, so for June it will be Report_June, Report_July and so on for other month whenever I click the SaveAs Button.
I want to save the complete WorkBook which has 4 sheet in total but I want only the data available till last column populated in each sheet and till last row, I don't want What I have put in Column X to be SaveAs in my new file . Eg Check Sheet July column X has some info tab.

So SaveAs Complete workbooj with Name Report_MonthName without changing the worksheet name present in the workbook and only the data till last row and last column, beyond that I don't want in my SaveAs file.

Attachment 872512

[jdelano]

I think, in total, you have all the code you need to get what you want done.

Perhaps without this, how to figure out the number of rows and columns are in use.
https://www.thespreadsheetguru.com/last-row-column-vba/

    Dim lastColumn As Integer
    Dim lastRow As Long
        
    ' how many rows and columns have data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

Good luck with your project.

I'll add another workbook I helped someone else with here on Excel Forum that concatenates the sheets into a single CSV file,
maybe with that you can do everything you're looking to do.

[Nyay]

It is actaully different, from what I want.
I just want to SaveAs the present workbook, but in the new file(Post SaveAs in .csv) I want the all the sheet from where I am saving the file just the till a paritcular column and not what is populated post that.

WorkBook Name post SaveAs = Report_MonthName