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-03-17T08:48:40+00:00

    Yes, you can't run the code directly. I have trimmed many columns down.

    Like I said the original data set has 40+ columns and 10,000 rows. 

    It makes no sense including them all so I made a sample data set for demonstration.

    I was trying to explain the whole process with words from the beginning cuz data set may not make much sense unless you know how they are related. 

    You can just add the addition code to the original code from what you understand. I will adjust it to my data set.

    Using the unique number/numbers. I do a lookup from the wsSource table and save those files  to put it simple. 

    How to keep those range of unique numbers in the memory and process them one at a time with relevant filters and save them.

    It is better we execute it by breaking down step by step instead of trying to understand the dataset.  

    Scenario 1

    We need to adjust the code to store a range of unique numbers in the memory instead of one unique number and pull the data from wsSource one at a time and save them with the unique number and description in any cell of column 2 Databodyrange in wsSource as file name.

    Scenario 2

    is tricky this has to be a separate macro we shall get back to it once we complete Scenario 1.

    Was this answer helpful?

    0 comments
  2. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-03-17T03:07:44+00:00

    I have been looking at the workbooks every day and I just can't make head nor tail of what you are trying to do. The code examples I have provided do not run because of workbook/worksheet names that are not the same in the example workbooks you have provided and the number of columns do not match the columns numbers referenced by the code.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-03-14T11:39:10+00:00

    Sure, take your time and let me know if you have any questions.

    Was this answer helpful?

    0 comments
  4. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-03-13T10:38:03+00:00

    OK. got the files now and will have a look at them tomorrow.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2021-03-13T10:03:48+00:00

    Try thislink

    Was this answer helpful?

    0 comments