This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have a simple macro. To copy values from one workbook to another in this case ML to MCR based on the user input number.
Number format can be e.g M5888-9087, M1990A 600111512, 453564034421 822349224.
Macro works with numbers like M5888-9087 but if I enter M1990A 600111512 or 453564034421 822349224 it copies all the rows instead of filtering M1990A 600111512 or 453564034421 822349224 and copying those selected rows.
Space in between is the culprit. How to overcome this?
Does changing the filter helps? I see other filters in object library.
Sub MCR()
Dim myInp As Variant
myInp = VBA.Interaction.InputBox(Prompt:="Enter the number", Title:="MCR Macro")
Windows("ML.xlsx").Activate
Worksheets("Full-View").Select
Worksheets("Full-View").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=myInp
Range("Table1[[#Headers],[Serial]]").Select
ActiveCell.Offset(1, 7).Range("Table1[[#Headers],[Serial]]").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("MCR Macro.xlsm").Activate
Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Here is the linkwhile formatting the numbers make sure the leading zero is preserved, excel removes the zero when the column is formatted to number. Just a heads up.
Yes! Just copy the three columns into a new workbook and upload that please..
I have the sample data in the unique list worksheet in the template. If I where to upload a workbook I would just copy paste those three columns into a new workbook nothing more than that.
For sake of testing the macro run that data would be sufficient I guess.
**Your comment:**I can either paste them in the template as shown below or keep them in a separate file if necessary.
If you upload a workbook with the data then I will copy the worksheet into the Macro Workbook and insert some VBA code in the project to update the Unique List worksheet as required.
Yes, as you said you had some issues going on I wanted to give some time.
I will explain it step by step.
In the new template which you must be having from the link. If not, I have them here.
There is an additional column to capture the unique number. Earlier we used the unique number to search in wsSource file but did not bring the number into the file. In addition to that we use one unique number to filter the wsSource after populating the template for that unique number we save the file with the unique number as file name.
In the present scenario, we look for multiple unique numbers in the wsSource and bring the associated rows including the unique numbers.
So where do we get these unique numbers from? you might ask. I can either paste them in the template as shown below or keep them in a separate file if necessary.
As you can see from the above screenshot the loop for column A is decided based on the column C all unique numbers go in to one file for GEN1 and saved as MCR-GEN-Output sh A4 value-001_Category sheet E3 value.xls
Categories sheet follow the same process as followed in the previous macro. Except the column E will be filled with column B value in the above screenshot.
If its not clear let me which part needs to be explained.