VBA InputBox Issue

Excellent It worked!

Some fine tuning is required when I open the workbooks I want the default sheet to be the coversheet and in the coversheet B1 cell, MCR-Unique number-002. I need to input unique number e.g MCR-03300404000Y-002.

If I set the page zoom for each sheet in the template workbook that will replicated for all the copies I make I guess.

One question rngUniqVal was declared as range it was looped with For Each therefore it does not require set? but rngUniqVal is not a collection.

I need to adjust the code to the actual dataset. Lets see if I encounter any issues.

@ExcelFunTension,

when I open the workbooks I want the default sheet to be the coversheet. No problem. We can select the Coversheet and position cursor wherever required before saving and closing.

I need to input unique number e.g MCR-03300404000Y-002.

Where is the unique number derived from? (ie. Is it in the source data somewhere or do you have to specifically enter it?)

Is the Unique number different for each report produced or is it the same for all reports in the batch of reports?

If it needs to be specifically entered  by the user then we can provide an InputBox to enter it at the appropriate time.

If I set the page zoom for each sheet in the template workbook that will replicated for all the copies I make I guess. Not sure but I will test. If it doesn't copy the zoom then It is easy enough to read the zoom on the template and apply it to the new worksheet.

One question rngUniqVal was declared as range it was looped with For Each therefore it does not require set? but rngUniqVal is not a collection. No! It does not need to be specifically set. It is the individual cell in each loop. It loops through the list of individual unique values created in the temporary worksheet where the first column of the table is copied to and then duplicates removed.  Not sure if this is the best  way but maybe think of it as a collection of only one cell.

I am thinking of altering the code for creating the Unique list( duplicates removed). Instead of using a temporary worksheet, it will be better if I add a sheet to the Macro workbook and simply clear the list for each new run of the code. 

And a comment about the first column of the Source Data. The header is "Unique number" but in fact they are not unique; there are multiple same values. Unique is really pertains to after duplicates removed. But this is just a comment and it doesn't make a difference to how the code works.

I will edit my code and then after you let me know about the Unique number on the Coversheet then I will upload revised code for you.

About Unique number yes it is unique after removing the duplicates. For input box the unique number is the query number to filter the table. Same goes in the coversheet e.g MCR-Unique number-002

Column A in wsSource is the unique number after removing the duplicates. 

If I adjust the zoom in template it replicates I tested it. 

In the below code 

For Each rngUniqVal In rngUnique

        wbMacro.Sheets(2).Copy              

        Set wbOutput = ActiveWorkbook        

        With wbMacro

            For s = 3 To .Worksheets.Count  

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

            Next s

        End With

        Set wsOutput = wbOutput.Sheets("Output")   

        With wsOutput

            lngLastRow = LastRow(.Cells)

            If lngLastRow < 4 Then lngLastRow = 4   

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

        End With

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.

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

wsOutput has no data yet why clear contents was used.

if LastRow(.Cells) = 4 

Then Range(4, 4) .ClearContents I did not understand it.

I am thinking of altering the code for creating the Unique list( duplicates removed). Instead of using a temporary worksheet, it will be better if I add a sheet to the Macro workbook and simply clear the list for each new run of the code.

I actually expected the same that might be useful for documentation or to check for how many unique numbers the workbooks were created. I saw the code creates wsTemp and deletes at the end. That is also good leaves no remnants or new sheets.

Now I realized when I uploaded the workbooks they had the previous code in them that I should have deleted before uploading. That code is no good to run on the sample dataset.

ExcelFunTension,

As per my previous post, I have created an additional worksheet called "Unique List" in the Macro code workbook. The code actually copies the first 2 columns from the Source data to this worksheet. Then the duplicates are removed based on only the first column and leaving a unique list in the first column and the associated description in the second column. (I assumed that the description is the same for all same numbers in the first column so I hope this assumption is correct).

Code has been modified to reflect the above.

NOTE: I modified the source records with additional dummy data and Unique Numbers to provide additional data for testing. I also modified the code for the specific columns of data to copy because the code example you provided was copying columns of data that I didn't have.

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 Unique list remains after the code has run if you wish to view it. Also, the code activates the sheet when finished.

I still don't understand where the number comes from for cell B1 on Coversheet. Cells B3 and B4 are populated from the table created in worksheet "Unique List" in the Macro workbook.

To find the code to populate above 2 cells 

• Click anywhere in the sub
• Select Menu Item Edit -> Find and in "Find what" enter Coversheet  and click "Find Next".
• You should find the following code that populates the cells B3 and B4 and also creates the FileName from the values.

'Populate Output CoverSheet with Unique Number and Description from the Unique List in the Unique List sheet

        With wbOutput.Worksheets("Coversheet")

            .Cells(3, "B") = rngUniqVal.Value       'Unique Number

            .Cells(4, "B") = rngUniqVal.Offset(0, 1).Value  'Description

            .Range("B3:B4").HorizontalAlignment = xlLeft    'Left justify in case one or both of the values are numeric

            'Create the New FileName from the Unique Number and Description

            strFileName = .Cells(3, "B").Value & " " & .Cells(4, "B").Value

        End With

I have uploaded the edited workbooks to the following link:      Link to zipped files

I guess, I can take B3 value from worksheet "Unique List" in the Macro workbook to fill B1 in the coversheet.

With wbOutput.Worksheets("Coversheet")

            .Cells(3, "B") = rngUniqVal.Value       'Unique Number

            .Cells(4, "B") = rngUniqVal.Offset(0, 1).Value  'Description

            .Cells(1, "B") = "MCR-" & rngUniqVal.Value & "-002"  'Unique Number

End With

In the below code 

For Each rngUniqVal In rngUnique

        wbMacro.Sheets(2).Copy              

        Set wbOutput = ActiveWorkbook        

        With wbMacro

            For s = 3 To .Worksheets.Count  

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

            Next s

        End With

        Set wsOutput = wbOutput.Sheets("Output")   

        With wsOutput

            lngLastRow = LastRow(.Cells)

            If lngLastRow < 4 Then lngLastRow = 4   

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

        End With

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.

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

wsOutput has no data yet why clear contents was used.

if LastRow(.Cells) = 4 

Then Range(4, 4) .ClearContents I did not understand it.