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.
I am actually attempting to do it without Power Query step it will be difficult as the files I switch between are huge not sure if Do events will help.
I have few questions on the previous code.
That's okay if you get time give it a try as it going to take long time for me. If not that is fine.
Unfortunately, I don't see myself having the time to continue with this project. I have so many things on my plate at the moment with other personal issues that are going on.
I have already provided quite a lot of code examples for you and as this appears to be a variation of the previous solutions, I think that you should try to continue with this yourself. I think that you should be able to follow the code examples that I have provided to work out what you require to continue the the project and I think that you will learn a great deal more from doing it yourself instead of me doing it for you.
Its not that I changed my mind all three scenarios are required only thing is that they have minor changes from each other. Three templates serve different purpose going forward, I may have minor changes and make another template, although there is no requirement for new template.
Link to folder
You won't understand all three scenarios at once, so I had to go one by one.
There is one addition column in both Output and Categories sheet and the input in the unique list got changed now it has to loop based on column C for each unique number in unique list sheet then loop through each unique numbers if found in wsSource.
GEN1>>>unique number, 03300404000Y >>> unique number found in wsSource >>> capture all the fields into the template both output sheet and categories sheet*.
Repeat for all unique numbers under GEN1 then save as MCR-GEN-Output sh A4 value-001_Category sheet E3 value.xls
*For categories sheet Name column is taken from column B, unique list.
(old template) has no unique number column
(new template) unique number column added
Find unique numbers in wsSource under GEN1 populate in Output and Categories sheet save as MCR-GEN-Output sh A4 value-001_Category sheet E3 value.xls and repeat for GEN2...
Scenario 3
As stated above another change is in the categories sheet, Name column values taken from the unique list column B earlier the name is taken from wsSource Name column.
For all the unique numbers under GEN1 the Name column in the categories sheet is same.
In contrast, in the output sheet the Name values are from wsSource Name column NOT from column B.
If its not clear you can ask questions later you can start the code.
To answer your question unique list input has changed and additional loop will be introduced.
The code for Scenario 2 does not involve looping within a set of unique numbers.
Scenario 2
Unique number >>> wsSource >>> if found populate the templates Output sheet and Categories sheet.
Scenario 2 flow
Unique number1 >>> wsSource >>> if found populate the templates Output sheet and Categories sheet. >>> save as MCR-Unique number-001_Descr
Unique number2 >>> wsSource >>> if found populate the templates Output sheet and Categories sheet. >>> save as MCR-Unique number-001_Descr
.
.
.
Unique number14 >>> wsSource >>> if found populate the templates Output sheet and Categories sheet. >>> save as MCR-Unique number-001_Descr
After completion we end up with 14 filled templates
Scenario 3 flow
GEN1 >>> unique number >>> if unique number found in wsSource >>> capture all the fields into the template both output sheet and categories sheet*.
Repeat for all unique numbers under GEN1 then save as MCR-GEN-Output sh A4 value-001_Category sheet E3 value.xls
GEN2 >>> unique number >>> if unique number found in wsSource >>> capture all the fields into the template both output sheet and categories sheet*.
Repeat for all unique numbers under GEN2 then save as MCR-GEN-Output sh A4 value-001_Category sheet E3 value.xls
.
.
.
GEN4 >>> unique number >>> if unique number found in wsSource >>> capture all the fields into the template both output sheet and categories sheet*.
Repeat for all unique numbers under GEN4 then save as MCR-GEN-Output sh A4 value-001_Category sheet E3 value.xls
*For categories sheet Name column is taken from column B, unique list sheet.
After completion we end up with 4 filled templates
Notice the change in the output templates Scenario 2, 14 vs Scenario 3, 4.
I hope you saw my previous reply. Let me know if you have any questions.
I haven't given it any priority because I don't understand what you want changed. Maybe I am mis-interpreting your replies but you seem to be continually changing your mind on what is required. I asked the following question ages ago and still have not seen an example as I asked for. You did provide an example of the Filename but not what is incorrect in the output data.
I don't really understand what you want changed here. Can you provide me some screen shots of what is being created with the existing program and then further screen shots with what you want it changed to and the new part make it red font.
Also, I can't download your latest because it has expired.