Nujat Tasnim

Hi JASON,
Thank you for your comment. According to your comment, I understand that you want to alter the code so that you don’t have to choose a worksheet but instead, it enters new data on the worksheet you are currently on.

For this, you don’t need any ListBox named ListBox1. Follow the below steps:
● In Step 1, while developing the UserForm to create the Data Entry Form, you don’t need to put ListBox1 as there will be no selection option according to your query.

So the UserForm will look like the following image.

● Now, in Module 1 insert the following code and save it.

Sub Run_UserForm()
UserForm1.Caption = “Data Entry Form”
Load UserForm1
UserForm1.Show
End Sub

Now, you are good to go. You will not have to choose a worksheet instead, it enters new data on the worksheet you are currently on.

Here is a sample image. I have entered data in the worksheet named Washington.

Hopefully, you will be able to solve your problem now. Please feel free to reach out to us with any other questions or you can send us your Excel files as well.

Regards
Nujat Tasnim
Exceldemy.

Hi BICKY,
Thank you for your comment. According to your comment, I understand that you have a folder named SALE DATA and in that folder, you have another 3 folders named 2021, 2022 and 2023. Each sales year folder has two folders (JAN, and FEB) and then an Excel workbook. You want to collect sales data from all 3 years in a single workbook.

To solve this issue follow the below steps:
● Insert a new module and copy and paste the following code.

Sub CollectSalesData1()
    Dim baseFolder As String
    Dim year As Variant
    Dim month As Variant
    Dim yearFolder As String
    Dim monthFolder As String
    Dim fileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim targetStartRow As Long
    Dim yearStartRow As Long

     ' Set the base folder path and arrays for years and months
    baseFolder = "D:\SALE DATA"
    years = Array("2021", "2022", "2023")
    months = Array("JAN", "FEB")

    ' Set the target sheet where you want to collect the data
    Set targetSheet = ThisWorkbook.Sheets("Sheet1")
    targetStartRow = 2
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    For Each year In years
        yearStartRow = targetStartRow ' Reset the starting row for each year
        For Each month In months
            yearFolder = baseFolder & "\" & year & "\" & month
            fileName = Dir(yearFolder & "\*.xlsx")
            
            Do While fileName <> ""
                Set wb = Workbooks.Open(yearFolder & "\" & fileName)
                Set ws = wb.Sheets("Sheet1") ' Change to the appropriate sheet name

                ' Calculate the next available row for the current year and month
                Dim nextAvailableRow As Long
                nextAvailableRow = Application.WorksheetFunction. _
                Max(yearStartRow, targetStartRow)

                ' Copy data from source sheet to target sheet
                lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
                ws.Range("B5:D" & lastRow).Copy targetSheet. _
                Cells(nextAvailableRow, 2)
                
                yearStartRow = nextAvailableRow + lastRow - 4 ' Update starting row for next iteration
                
                wb.Close SaveChanges:=False
                fileName = Dir
            Loop
        Next month
' Update the targetStartRow to the next available row for the next year
        targetStartRow = yearStartRow
    Next year
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

● Set the base folder path and the target sheet where you want to collect the data according to your PC.
● Run the macro by pressing F5.

You can see that the macro successfully extracted data to the new worksheet. You can download the Excel file below.
Answer.xlsm
Hopefully, you will be able to solve your problem now. Please feel free to reach out to us with any other questions or you can send us your Excel files as well.
Regards
Nujat Tasnim
Exceldemy.

Hi LESLIE,
Thank you for sharing your valuable feedback. We appreciate it very much. I understand your confusion. It is possible that a cell appears to be empty but the ISBLANK function returns FALSE for one of these 3 reasons:
● A regular space is present in the cell.
● A non-breaking space is present in the cell.
● It contains a zero-length string.

A zero-length string, also known as an empty string, is a string with zero characters; as a result, when a cell contains a zero-length string, the LEN function returns 0. In Excel, both blank and empty cells appear empty; however, blank cells include a formula or value that evaluates to or represents a zero-length string, but empty cells do not.

In this image, you can see that cell C7 appears blank, but it’s not! It contains a zero-length string created by entering a single apostrophe (‘) and formatted like other values: General.

You can find out the Excel cells which contain zero-length strings by applying the following formula:
● Insert the following formula in cell D5 and press Enter.
● Now AutoFill the rest of the column’s cells to apply the same formula.

=IF(AND(LEN(C5)=0,NOT(ISBLANK(C5))),"Zero-length string","Not a zero-length string")

After that, you can remove the zero-length strings manually. Just select the cells and press the Delete key. Then sort to get the desired output.
Hopefully, you will be able to solve your problem now. Please feel free to reach out to us with any other questions or you can send us your Excel files as well.

Regards,
Nujat Tasnim
Exceldemy.

Hi BELINDA H,

Thank you for sharing your valuable feedback. We appreciate it very much. First of all, I apologize for the inconvenience. This code worked perfectly when I ran it again. But based on your query, we have identified some possible reasons for this error.

The error you’re encountering in the line Set sWB = Workbooks.Open(Filename:=CurrentF) is likely due to an issue with the file path or the file itself. There are a few possible reasons for this error:

1. Incorrect File Path: Ensure that the CurrentF variable contains a valid file path. Double-check that the file path is correct and that the file exists at that location.

2. File in Use: If the file you’re trying to open is already open in another instance of Excel or by another user, you may encounter an error. Make sure the file is not being used by another process.

3. File Format Compatibility: The code is designed to open Microsoft Excel workbooks with the extensions .xls, .xlsx, or .xlsm. If you’re trying to open a file with a different extension or a file that is not a valid Excel workbook, you may encounter an error. Ensure that the file you’re trying to open is in a compatible format.

4. Security Settings: If your Excel application has certain security settings enabled, such as macro security or protected view, it may prevent the opening of certain files. Check your Excel security settings and make sure they allow for opening files from the specified location.

By troubleshooting these possibilities, hopefully you should be able to identify the reason behind the error and resolve it accordingly. Please feel free to reach out to us with any other questions or you can send us your Excel files as well.