VBA InputBox Issue

Anonymous
2021-03-05T18:47:39+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

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.

0 comments

99 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-05-16T15:54:11+00:00

    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.

    Image

    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.  

    Image

    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.

    Was this answer helpful?

    0 comments
  2. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-05-10T03:38:30+00:00

    Sure I can do that. I will post the output images in my next reply.

    Wondering what has happened. Do you still need assistance with this?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-05-02T15:46:55+00:00

    Sure I can do that. I will post the output images in my next reply.

    Was this answer helpful?

    0 comments
  4. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-04-30T03:25:16+00:00

    Retaining the column order is not much of a problem now that I know it.

    The variant for Scenario 2 link. 90% of the code stay same as you can see from the template. Only addition is the Unique number column in both the Output and Categories sheets. Some changes to the Coversheet.

    The Unique Number list has the numbers, name and type to populate the templates. This can come from a separate excel or I can paste it directly. Based on the type the template gets populated.

    In categories sheet the Name column gets its name from Unique list sheet instead of wsSource name column.

    File Name saved as follows MCR-GEN-Unique Number-001_Name

    Unique Number and Name from Unique list sheet.

    Word GEN and 001 stays same for all the files.

    Let me know if it not clear. 

    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.

    With the file name you want changed, provide me with an example name from the existing file name and then an example name of the changed filename with the font of the changes in red.

    I am hoping that I will have time over the coming weekend to do some more work on it if you can get me the information by then.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2021-04-23T16:11:35+00:00

    Take your time just save the folder contents.

    You can start it next week or whenever it suits you.

    Was this answer helpful?

    0 comments