How to save all the Excel file in same folder with the given format

[ramya0313]

Hi i want to save all the excel output files in same

folder ,i have a vba code but the files are not saving in the folder. The file should be saved in the format with first 5 digits in F column that is (example 1396A) format
01_03_1396A_yyyymmdd

VBA CODE

Sub SplitSheetIntoMultipleSheetsBasedOnColumn()
Dim objWorksheet As Excel.Worksheet
Dim nLastRow, nRow, nNextRow As Integer
Dim strColumnValue As String
Dim objDictionary As Object
Dim varColumnValues As Variant
Dim varColumnValue As Variant
Dim objSheet As Excel.Worksheet
Dim FPath As String

Set objWorksheet = ActiveSheet
nLastRow = objWorksheet.Range("F" & objWorksheet.Rows.Count).End(xlUp).Row
Set objDictionary = CreateObject("Scripting.Dictionary")

For nRow = 2 To nLastRow
strColumnValue = objWorksheet.Range("A" & nRow).Value
If objDictionary.Exists(strColumnValue) = False Then
objDictionary.Add strColumnValue, 1
End If
Next

varColumnValues = objDictionary.Keys

For i = LBound(varColumnValues) To UBound(varColumnValues)
varColumnValue = varColumnValues(i)
Set objSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
objSheet.Name = varColumnValue
objWorksheet.Rows(1).EntireRow.Copy objSheet.Rows(1)
For nRow = 2 To nLastRow
If CStr(objWorksheet.Range("F" & nRow).Value) = CStr(varColumnValue) Then
objWorksheet.Rows(nRow).EntireRow.Copy
nNextRow = objSheet.Range("A" & objSheet.Rows.Count).End(xlUp).Row + 1
objSheet.Range("A" & nNextRow).PasteSpecial xlPasteValuesAndNumberFormats
End If
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
ws.Copy
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Next
objSheet.Columns("A:H").AutoFit
Next
End Sub

 

[OaklandJim]

If you want assistance you are more likely to get help if you help the helper so they do not need to guess about or recreate your data.

Pictures are not very helpful. If the data is not confidential post a link to your workbook(s). If necessary you can enter fake-but-realistic data before providing the link. Put the file on Dropbox, Box, 1Drive, Google Drive etc. Use the link icon above the message area. Make sure that other people can access the file!

Or, consider sharing relevant data using Mr Excel's excellent XL2BB addin that enables you to post a portion of a worksheet. See XL2BB - Excel Range to BBCode for details.

Regarding

i want to save all the excel output files in same folder

What do you mean by "save output files"? What is in the files?

 

[OaklandJim]

If I understand what you want it should be quite doable.

BUT, I tried to understand what your code is supposed to do.

Are new worksheets supposed to be named based on the values in column A or column F? Column A has duplicates. Column F does not have duplicates.

This code...

    Set objDictionary = CreateObject("Scripting.Dictionary")
    
    For nRow = 2 To nLastRow
    
        strColumnValue = objWorksheet.Range("A" & nRow).Value
        
        If objDictionary.Exists(strColumnValue) = False Then
            objDictionary.Add strColumnValue, 1
        End If
    
    Next

gathers values from Column A in the source worksheet then this code...

    varColumnValues = objDictionary.Keys
    
    For i = LBound(varColumnValues) To UBound(varColumnValues)
        
        varColumnValue = varColumnValues(i)
        
        Set objSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        
        objSheet.Name = varColumnValue

...seems to be using column A values as names for new worksheets created.

If I understand what you are trying to do this will not work as there are repeated values in column A. (You cannot have more than one worksheet with the same name in a workbook.)

What is this code supposed to do?

            For Each ws In ThisWorkbook.Sheets
                
                ws.Copy
                
                Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
                
                Application.ActiveWorkbook.Close False
            
            Next ws

As is it 1. copies each worksheet in ThisWorkbook, 2. Saves the ActiveWorkbook (which is still ThisWorkbook) with the name of the worksheet in the loop and 3. closes the ActiveWorkbook. Like I said, ThisWorkbook and ActiveWorkbook are the same because you never created a new workbook.

If I understand what you want to do -- create a workbook for each worksheet in the source worksheet -- you need to create a new workbook for each worksheet in the source Workbook (ThisWorkbook). Is that right?

Please try to describe the process that you want to accomplish, with more detail, so I can assist.

 

[OaklandJim]

Ok, so I understand now that you use the dictionary to get UNIQUE values from Column A. Disregard my comments about duplicate values in Column A.

But I am still confused. Can you tell me what this code is supposed to do?

        varColumnValue = varColumnValues(i)
        
        Set objSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        
        objSheet.Name = varColumnValue
        
        objWorksheet.Rows(1).EntireRow.Copy objSheet.Rows(1)
        
        For nRow = 2 To nLastRow
            
            If CStr(objWorksheet.Range("F" & nRow).Value) = CStr(varColumnValue) Then
                
                objWorksheet.Rows(nRow).EntireRow.Copy
                
                nNextRow = objSheet.Range("A" & objSheet.Rows.Count).End(xlUp).Row + 1
                
                objSheet.Range("A" & nNextRow).PasteSpecial xlPasteValuesAndNumberFormats
            
            End If

I am trying to understand how column F is involved. It seems that you are trying to determine if the value in Column F = the value in Column A. But there are no matches like that.

 

[OaklandJim]

I am still trying to understand your code and your objective.

In the code, for your example data, it seems that you are creating two new worksheets, one named surapet and one named vinayagar (it looks like there is another letter but I cannot tell.) Apparently those two new worksheets contain the same data as the starting worksheet. Is that right?

So after adding two new worksheets there is a total of three worksheets in the starting workbook. Is that correct.

It SEEMS that that you want five new workbooks created, which contain the two new sheets. Is that correct?

Do the new workbooks contain all three worksheets in the starting workbook or just the two new worksheets.

Apparently, the new workbooks' names are based on the values in column F? Correct?

In your example data for column F there are two cells with the same five starting digits: 1396A. One ends in 0001A and the other ends with 0003A.

You want the date in file names in yyyyddmm format. I understand that. Then you say...

The file should be saved in the format with first 5 digits in F column that is (example 1396A) format

But the example you give for file name shows the file name as

01_03_1396A_yyyymmdd

What is the 01_03 part? Did you mean that two of the workbooks have these names: 01_1396A_yyymmdd.xlsx and 03_1396A_yyymmdd.xlsx

 

[ramya0313]

Hi actually I need to split the Excel file based on the first five digit of the f column like entire 1396A will be splitted in single file and 0045A in single file like wise ..the code is working fine if I remove the file path
Sub SplitSheetIntoMultipleWorkbooksBasedOnColumn()
Dim objWorksheet As Excel.Worksheet
Dim nLastRow As Integer, nRow As Integer, nNextRow As Integer
Dim strColumnValue As String
Dim objDictionary As Object
Dim varColumnValues As Variant
Dim varColumnValue As Variant
Dim objExcelWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set objWorksheet = ActiveSheet
nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row
Set objDictionary = CreateObject("Scripting.Dictionary")
For nRow = 2 To nLastRow
strColumnValue = Left(objWorksheet.Range("F" & nRow).Value, 5)
If objDictionary.Exists(strColumnValue) = False Then
objDictionary.Add strColumnValue, 1
End If
Next
varColumnValues = objDictionary.Keys
For i = LBound(varColumnValues) To UBound(varColumnValues)
varColumnValue = varColumnValues(i)
Set objExcelWorkbook = Excel.Application.Workbooks.Add
Set objSheet = objExcelWorkbook.Sheets(1)
objSheet.Name = objWorksheet.Name
objWorksheet.Rows(1).EntireRow.Copy
objSheet.Activate
objSheet.Range("A1").Select
objSheet.Paste
For nRow = 2 To nLastRow
If Left(CStr(objWorksheet.Range("F" & nRow).Value), 5) = CStr(varColumnValue) Then
objWorksheet.Rows(nRow).EntireRow.Copy
nNextRow = objSheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1
objSheet.Range("A" & nNextRow).Select
objSheet.Paste
objSheet.Columns("A:H").AutoFit
End If

Next
Next
End Sub

 

[ramya0313]

But the problem is the file are separated and it is not saving in the same folder like the file is separated as book1,book2 etc
I need to set the location to save the files in the same folder as macro and I want to save the files in

01_03_1396A_yyyyddmm
01_03_0045A_yyyyddmm
01_03_0001A_yyyyddmm

The above mentioned 01_03 is common for all the files(client requirement)

Can u please help with the above code

 

[ramya0313]

the below mentioned code is working fine it is splitting the entire Excel sheet based on first five digits on colum value

Guide me to set the location to save the files
Thank you

 

[ramya0313]

I am still trying to understand your code and your objective.

In the code, for your example data, it seems that you are creating two new worksheets, one named surapet and one named vinayagar (it looks like there is another letter but I cannot tell.) Apparently those two new worksheets contain the same data as the starting worksheet. Is that right?

So after adding two new worksheets there is a total of three worksheets in the starting workbook. Is that correct.

It SEEMS that that you want five new workbooks created, which contain the two new sheets. Is that correct?

Do the new workbooks contain all three worksheets in the starting workbook or just the two new worksheets.

Apparently, the new workbooks' names are based on the values in column F? Correct?

In your example data for column F there are two cells with the same five starting digits: 1396A. One ends in 0001A and the other ends with 0003A.

You want the date in file names in yyyyddmm format. I understand that. Then you say...



But the example you give for file name shows the file name as


What is the 01_03 part? Did you mean that two of the workbooks have these names: 01_1396A_yyymmdd.xlsx and 03_1396A_yyymmdd.xlsx

I am still trying to understand your code and your objective.

In the code, for your example data, it seems that you are creating two new worksheets, one named surapet and one named vinayagar (it looks like there is another letter but I cannot tell.) Apparently those two new worksheets contain the same data as the starting worksheet. Is that right?

So after adding two new worksheets there is a total of three worksheets in the starting workbook. Is that correct.

It SEEMS that that you want five new workbooks created, which contain the two new sheets. Is that correct?

Do the new workbooks contain all three worksheets in the starting workbook or just the two new worksheets.

Apparently, the new workbooks' names are based on the values in column F? Correct?

In your example data for column F there are two cells with the same five starting digits: 1396A. One ends in 0001A and the other ends with 0003A.

You want the date in file names in yyyyddmm format. I understand that. Then you say...



But the example you give for file name shows the file name as


What is the 01_03 part? Did you mean that two of the workbooks have these names: 01_1396A_yyymmdd.xlsx and 03_1396A_yyymmdd.xlsx

Hi actually I need to split the Excel file based on the first five digit of the f column like entire 1396A will be splitted in single file and 0045A in single file like wise ..the code is working fine if I remove the file path
Sub SplitSheetIntoMultipleWorkbooksBasedOnColumn()
Dim objWorksheet As Excel.Worksheet
Dim nLastRow As Integer, nRow As Integer, nNextRow As Integer
Dim strColumnValue As String
Dim objDictionary As Object
Dim varColumnValues As Variant
Dim varColumnValue As Variant
Dim objExcelWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set objWorksheet = ActiveSheet
nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row
Set objDictionary = CreateObject("Scripting.Dictionary")
For nRow = 2 To nLastRow
strColumnValue = Left(objWorksheet.Range("F" & nRow).Value, 5)
If objDictionary.Exists(strColumnValue) = False Then
objDictionary.Add strColumnValue, 1
End If
Next
varColumnValues = objDictionary.Keys
For i = LBound(varColumnValues) To UBound(varColumnValues)
varColumnValue = varColumnValues(i)
Set objExcelWorkbook = Excel.Application.Workbooks.Add
Set objSheet = objExcelWorkbook.Sheets(1)
objSheet.Name = objWorksheet.Name
objWorksheet.Rows(1).EntireRow.Copy
objSheet.Activate
objSheet.Range("A1").Select
objSheet.Paste
For nRow = 2 To nLastRow
If Left(CStr(objWorksheet.Range("F" & nRow).Value), 5) = CStr(varColumnValue) Then
objWorksheet.Rows(nRow).EntireRow.Copy
nNextRow = objSheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1
objSheet.Range("A" & nNextRow).Select
objSheet.Paste
objSheet.Columns("A:H"
).AutoFit
End If

Next
Next
End Sub

 

[ramya0313]

I am still trying to understand your code and your objective.

In the code, for your example data, it seems that you are creating two new worksheets, one named surapet and one named vinayagar (it looks like there is another letter but I cannot tell.) Apparently those two new worksheets contain the same data as the starting worksheet. Is that right?

So after adding two new worksheets there is a total of three worksheets in the starting workbook. Is that correct.

It SEEMS that that you want five new workbooks created, which contain the two new sheets. Is that correct?

Do the new workbooks contain all three worksheets in the starting workbook or just the two new worksheets.

Apparently, the new workbooks' names are based on the values in column F? Correct?

In your example data for column F there are two cells with the same five starting digits: 1396A. One ends in 0001A and the other ends with 0003A.

You want the date in file names in yyyyddmm format. I understand that. Then you say...



But the example you give for file name shows the file name as


What is the 01_03 part? Did you mean that two of the workbooks have these names: 01_1396A_yyymmdd.xlsx and 03_1396A_yyymmdd.xlsx

But the problem is the file are separated and it is not saving in the same folder like the file is separated as book1,book2 etc
I need to set the location to save the files in the same folder as macro and I want to save the files in

01_03_1396A_yyyyddmm
01_03_0045A_yyyyddmm
01_03_0001A_yyyyddmm

The above mentioned 01_03 is common for all the files(client requirement)

Can u please help with the above code