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 will share the file for better understanding
I don't understand your description and there is no file to download and try to ascertain what you want.
I did not previously realize that you wanted to run the code separately for each scenario and I combined both
Scenario 2 macro has to run for both the sheets as you did. We had another macro Scenario 1 which was my initial request which only deals with the Output sheet because the wsSource does not have all the data that is required for categories sheet once I pull all that into wsSource only then I can populate both Output and categories sheets in one go. So wsSource is different for both the macros. Therefore they both are different.
Scenario 1 Macro working good on actual data set.
Scenario 2 I will test on the actual data set.
Scenario 2 looks good.
For the Scenario 2 I want to make a variant that variant version will be used in particular cases. I attempted to do it but I am facing some difficulty in framing the code because it requires loop within a loop within a loop. May be there is a better way to do it.
What I am trying to do is after I paste a set of Unique numbers with out duplicates in the unique sheet.
The Unique numbers can also be taken from the excel by uploading the file.
See e.g. I want to loop through the unique numbers based on the Column B. It would loop through all AMN1 unique numbers and look in wsSource and loop though the rows and populate the rows in the template and save. Loop continues for column B until AMN4.
AMN1 to AMN4 can be named in any format we want. Those characters are only to separate the set of unique numbers that goes into the template.
The new addition in the template is it would also capture the unique numbers as well this time in the template. So there will be a new column before VC number in the output sheet and before Half Number in categories sheet.
I will share the file for better understanding.
e.g
Column A Column B
Unique Number1 AMN1
Unique Number2 AMN1
Unique Number3 AMN1
Unique Number4 AMN2
Unique Number5 AMN2
Unique Number6 AMN2
Unique Number7 AMN3
Unique Number8 AMN4
Unique Number9 AMN4
In answer to your comment as follows a few days ago.
While processing column W in wsSource. code introduces line breaks. but in the categories I use Wrap? I feel there is no need to introduce line breaks, only remove the leading part of column W.
It is not practical to simply use Wrap Text without the line feed. There is no way of controlling where the line will break. Generally it will be at a space or hyphen but depending on the length of the string, it might break in the middle of a word or if the column is too wide, it includes multiple words that were meant to be on separate lines.
Column AutoFit does not increment the width of a column when Wrap Test is on because the text wraps to fit the existing column width so therefore Excel thinks it does not require widening.
However, if the column is too wide wide it will decrement the width to the longest existing string. By using the Line Feeds the longest existing string across the column is controlled by length of text between the line feeds. Therefore one would think that AutoFit should work but unfortunately it will not increment the width. It is necessary to increment the width wider than ever likely to be required and then use AutoFit.
I reiterate my comment at the bottom of my last post re "System Analysis" prior to commencing the programming. The programmer writes the code as per the specifications provided and that is what I have attempted to achieve and I previously used the Categories as per the examples given. Please don't get upset about me saying this but for your future projects I want you to understand how important it is to get the specifications correct before commencing the programming.
Anyway the categories were not too difficult to change. I simply added another column to the table on Main Menu sheet and updated the category header in the code based on the first and last column of the table. Your Category with numbers must remain as is on the Template Worksheet in the Macro workbook. You can still add more categories to the table but you will need to update the template category worksheet with the extra categories. The loop for the categories is not limited to 4 because the code will process as many as there are in the table on the Main Menu worksheet.
I did not previously realize that you wanted to run the code separately for each scenario and I combined both . However Scenario 1 and Scenario 2 are now controlled with a CheckBox on the Progress Userform. If Categories are required then check the box before clicking the "Start" button. If the box is not checked then the categories sub is not called and also the categories worksheet is not copied to the Output workbook. I have changed the code at around lines 139 to 158 and used a separate Case statement for each template worksheet name to be copied instead of in one Case statement with comma separated list of worksheets. (Are you aware that you can see the cursor position line number on the toolbar in the VBA editor?)
Updated macro workbook and source data file at the following link. New Version 7
I think I did not explain it clearly. The box heading won't be category 1 to 4 and column E will be just Category in the template like in the screenshot I posted earlier. Since you were looping through the Category numbers macro won't work if I change the heading names in the template.
I was hoping you would hardcode the headings in the code so that I can change the code when there is a change to the template headings. They would be minor changes based on the revisions in future.
I did not expect you would make use of those numbers to loop.
Regarding the progress bar in Scenario 1. I mean we have two different macros so far.
Macro 1: Scenario 1 (That has no categories work sheet to be filled)
Macro 2: Scenario 2 (That has categories work sheet to be filled in addition to the other sheet)
I wanted to copy the code for the progress bar from second macro to the first macro as it might be useful there as well. But it does not work as expected.