VBA InputBox Issue

The below code could be reduced to one line

.Range(.Cells(4, "P"), .Cells(lngLastRow, "P")) = Date

.Range(.Cells(4, "P"), .Cells(lngLastRow, "P")).NumberFormat = "dd mmm, yyyy"

The Format function returns a String (Text) that can no longer be used as a date without conversion back to a date. I very much doubt that the one line of code to set the Number Format is going to make any  measurable difference to the time taken to run the code. In fact, if it is actually measurable, it would not surprise me if using the Format function takes longer due to the work involved in making the conversion to a string.

Also I noticed when there is a trailing space in wsSource >> Table >> Column Name it throws error code 9.

I had previously indicated that I had a problem with one of the headers in a previous post as per the following paragraph.

Note where I have copied the columns that I am now using the table column name in lieu of the column number (It makes it self documenting and you don't have to count up the columns. If any fail then probably due to a leading or trailing space on the column header in the table (I actually had one of these)

The table headers need to be corrected before using them because they must match exactly to the column name used in the code.

It is not possible to remove the additional spaces in the header from within the line of code. (It must be done before using the specific line of code).

Another problem is that they are not necessarily spaces, particularly if the data has been downloaded from elsewhere and they can be some other non printable character that appears like a space and the functions Trim and/or Clean do not remove them.

If you would like to provide me with a full copy of all of the headers in the table then I will provide some code that should ensure the headers are correct before the remaining code runs. 

To provide the list of column headers.

1. Add a worksheet to the workbook containing the table.
2. Select and copy the table headers.
3. Select the first cell in the new worksheet and PasteSpecial -> Values -> Transpose -> OK   (Values and Transpose both selected in the same dialog)
4. You should now have a vertical list of the table headers.
5. Copy the list and paste into your reply on the forum.
6. You can delete the extra worksheet.

wbMacro.Worksheets("Coversheet").Copy     This is the same as the following procedure in the Interactive Mode (ie. working directly on the worksheet)

Right Click a worksheet tab name -> Move or Copy -> Check "Create a Copy" box -> In the "To book" field click the DropDown -> Select (new book). It creates a new (additional) workbook with a copy of the worksheet. The new Workook becomes the ActiveWorkbook by default.

It is clear now how new workbook is created automatically. 

The code 

wbOutput.SaveAs Filename:=strPathAndFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

was sandwiched between Application.DisplayAlerts so I couldn't locate it until I did Ctrl + F

To make it more user friendly, I started to manually input the unique numbers in unique list worksheet instead of letting the code to run on the whole dataset. The data set has about 3000 unique values after removing duplicates. It would take long time to complete. 

The below code could be reduced to one line

.Range(.Cells(4, "P"), .Cells(lngLastRow, "P")) = Date

.Range(.Cells(4, "P"), .Cells(lngLastRow, "P")).NumberFormat = "dd mmm, yyyy"

I explored it today so though of letting you know.

.Range(.Cells(4, "P"), .Cells(lngLastRow, "P")) = Format(Date, "dd mmm, yyyy")

Also I noticed when there is a trailing space in wsSource >> Table >> Column Name it throws error code 9.

Where can I add Trim function in the below code.

lstObj.ListColumns("VC").DataBodyRange.SpecialCells(xlCellTypeVisible).Copy

        wsOutput.Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

            :=False, Transpose:=False

I will share the dataset for Scenario 2. I spent time going through the Scenario 1code all this time.

OK. I will try to explain. Firstly please be aware that the symbol -> means to select the next command/option identified after the symbol (or additional option where more than one action is required on the same dialog)

For Each rngUniqVal In rngUnique  This is the start of the loop through the Unique Numbers created in the worksheet "Unique List". The Unique numbers are created with previous code that copies the column from the source worksheet to the "Unique List" worksheet and then "Remove duplicates" is used to create the unique list. (This is the same as the "Remove duplicates" in the "Data" ribbon in the Interactive Mode).

wbMacro.Worksheets("Coversheet").Copy     This is the same as the following procedure in the Interactive Mode (ie. working directly on the worksheet)

Right Click a worksheet tab name -> Move or Copy -> Check "Create a Copy" box -> In the "To book" field click the DropDown -> Select (new book). It creates a new (additional) workbook with a copy of the worksheet. The new Workook becomes the ActiveWorkbook by default.

Set wbOutput = ActiveWorkbook   This assigns the new Workbook to a Workbook variable. As per the previous step, the New Workbook is the ActiveWorkbook. It does not matter what default name Excel assigned the New Workbook because it can now be referenced by the Workbook variable. (The new Worbook name will actually be Book1, Book2 etc and it will not clash with any other open Workbook but at this point the name is irrelevant).

For Each ws In wbMacro.Worksheets     This loops through all of the Worksheets in the Macro workbook starting from the left tab. Now we don't want all of the Worksheets, only specific ones. eg Don't want "Coversheet" because it has already been copied to the new workbook and we don't want the "Unique List" Worksheet in the "Output" Workbook. therefore we use the following code to identify the required worksheets to be copied.

Select Case ws.Name

         'Following line: If ws.Name matches any one of the names in the comma separated list then it is copied to the Output Workbook

          Case "Output", "Sheet2 not required", "Static Sheet3", "Legend"

               ws.Copy After:=wbOutput.Worksheets(wbOutput.Worksheets.Count)

End Select

ws.Copy After:=wbOutput.Worksheets(wbOutput.Worksheets.Count)  This line in the above code positions the Copied Worksheet as the next tab after the Worksheets that have already been copied to the new Output Workbook

The "Select Case" code could be replaced with a number of If statements but I find it easier to use the Select Case where the required options can be all on one line separated by commas. If you need more information on the Select Case then I suggest that you Google it and I am sure you will find lots of informative data on it.

Set wbOutput = ActiveWorkbook

ActiveWorkbook would be MacroWorkbook right? How new workbook is created automatically.    No! It is NOT the Macro Workbook. It is the new Output Workbook which becomes the ActiveWorkbook by default when it is created. See above for how the new workbook is created automatically when the worksheet is copied to a New Workbook.

myInp = rngUniqVal.Value

myInp is a range       No! myInp is a String; not a range.  rngUniqVal is a range of one cell that is created with the For Each rngUniqVal. myInp is the string value contained in rngUniqVal and is used to set the filter in the Table.  rngUniqVal.Value references the value contained in the range rngUniqVal

lstObj.Range.AutoFilter Field:=1, Criteria1:=myInp      This sets the filter to the string referenced by myInp.

strPathAndFileName = strPath & "" & strFileName & ".xlsx"

I don't see any save as method used here       This purely creates a string of the Path and File Name to which the Output Workbook is saved. Up to this point the Output workbook has not been saved. The following code saves the Workbook as an xlsx workbook. I use SaveAs rather than Save because Save saves in the in the default file type as set in Options and its existing name which will be "Book#" at this point. SaveAs with the FileFormat overrides the user's default save type option. If you select File -> Options -> Save then the first option is used to determine the user's default file type for the first save of a file. After the first save of a file, any future saves default to the existing file type.

wbOutput.SaveAs Filename:=strPathAndFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Hope this explanation helps.

Scenario 1 is completed. It worked very well on my actual data set.

I will update the data set for Scenario 2 everything in the code/wsSource stays the same except one new column from CSV file. In addition, to the output sheet we also populate the next sheet based on the filter criteria.

This part of the code it is not easy to understand.

For Each rngUniqVal In rngUnique

        wbMacro.Worksheets("Coversheet").Copy   'Copies the "Output Coversheet" worksheet to a new workbook. (New workbook is automatically added)

        Set wbOutput = ActiveWorkbook       'Assign new workbook to a workbook variable. (New workbook defaults to the ActiveWorkbook)

                                            'Note that there no need to rename the new workbook AT THIS STAGE. Simply reference by the workbook variable

      For Each ws In wbMacro.Worksheets

            'Using Select case allows list of sheets separated by a comma for matches.

            Select Case ws.Name

                'Following line: If ws.Name matches any one of the names in the comma separated list then it is copied to the Output Workbook

                Case "Output", "Sheet2 not required", "Static Sheet3", "Legend"

                    ws.Copy After:=wbOutput.Worksheets(wbOutput.Worksheets.Count)

            End Select

        Next ws

        Set wsOutput = wbOutput.Sheets("Output")   'Assign the new "Output" worksheet to a worksheet variable.

        myInp = rngUniqVal.Value

************************************************************************************************************

Set wbOutput = ActiveWorkbook  

ActiveWorkbook would be MacroWorkbook right? How new workbook is created automatically. 

myInp = rngUniqVal.Value

myInp is a range 

lstObj.Range.AutoFilter Field:=1, Criteria1:=myInp

lstObj know that it needs to loop through one value at a time?

strPathAndFileName = strPath & "" & strFileName & ".xlsx"

I don't see any save as method used here

I guess, I can take B3 value from worksheet "Unique List" in the Macro workbook to fill B1 in the coversheet. If your suggested code does what is expected then go with it.

wbMacro.Sheets(2).Copy

The above code will automatically create a new workbook? from the code I see cover sheet is being copied then workbooks.add and paste is required I was hoping. No Paste required at this stage of the code. The data is populated when the code loops through the source data, sets the filter then copies and pastes the visible data.

Following code is now obsolete. It is a legacy from earlier version where we were using the InputBox. I have now deleted it.

With wsOutput

lngLastRow = LastRow(.Cells)

If lngLastRow < 4 Then lngLastRow = 4

.Range(.Rows(4), .Rows(lngLastRow)).ClearContents

End With

if LastRow(.Cells) = 4

Then Range(rows(4), Rows(4)). ClearContents I did not understand it. Code is deleted now but the reason for setting the variable to 4 was if no data (only column headers) then last used row will be 3 and we did not want to delete row 3 so set it to 4. 

.Range(.Rows(4), Rows(4)) is actually row 4.  If it were .Range(.Rows(4), .Rows(7)) you would understand that it is row 4 to row 7 but row 4 to row 4 is simply row 4.

In my latest code I have replaced the copy templates code. Rather than loop through sheet numbers, I have looped through all sheets and I have used a Select Case and actually identify the sheets by their name. Reason for this is in case sometime in the future the sheet positions are changed. also you might decide that you need additional sheet/s and with the modified code you only need to add the sheet names to the comma separated list. so you can find the change in the uploaded workbook, it is the following code.

wbMacro.Worksheets("Coversheet").Copy   'Copies the "Output Coversheet" worksheet to a new workbook. (New workbook is automatically added)

        Set wbOutput = ActiveWorkbook       'Assign new workbook to a workbook variable. (New workbook defaults to the ActiveWorkbook)

                                            'Note that there no need to rename the new workbook AT THIS STAGE. Simply reference by the workbook variable

        'Copy remaining worksheet templates as per Case comma separated list to the new workbook (Last sheet Unique List should not be required in the Output workbook)

        For Each ws In wbMacro.Worksheets

            'Using Select case allows list of sheets separated by a comma for matches.

            Select Case ws.Name

                'Following line: If ws.Name matches any one of the names in the comma separated list then it is copied to the Output Workbook

                Case "Output", "Sheet2 not required", "Static Sheet3", "Legend"

                    ws.Copy After:=wbOutput.Worksheets(wbOutput.Worksheets.Count)

            End Select

        Next ws

I have uploaded a new zipped file to the following link:  Updated workbook files