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-06-03T22:59:08+00:00

    I hope you saw my previous reply. Let me know if you have any questions.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-05-19T00:40:54+00:00

    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.

    Was this answer helpful?

    0 comments
  3. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-05-18T20:00:40+00:00

    Yes! Just copy the three columns into a new workbook and upload that please..

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-05-18T14:47:10+00:00

    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.

    Was this answer helpful?

    0 comments
  5. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-05-18T06:19:39+00:00

    **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.

    Was this answer helpful?

    0 comments