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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2021-03-24T14:10:31+00:00

    Extra space appeared when I copied some unique numbers from wsSource and pasted into a new workbook and converted into a table. Then for one column (Name column) I had that issue rest are fine.

    Scenario 2 link

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-03-27T14:04:51+00:00

    I have attached the folder for Scenario 2 link

    Was this answer helpful?

    0 comments
  3. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-03-28T11:30:57+00:00

    I don't understand how to set the criteria to extract the required data for the Categories.

    For Category 1 if I set the Category filter to to Category_1 and the Code to no blanks then I can match the example you have provided for Category 1 but I am not sure that is what is required.

    For Category 2 and Category 3 I have no idea what criteria I am required to use to get anything like the data in your examples.

    Please set out step by step what criteria I need to use to extract the data for each criteria report.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-03-28T12:29:57+00:00

    Yes, Ideally there won't be any blanks for Category_1 for this example there are blanks but no need to include blanks filter in the code.

    For  Category_2 Column T and Column U has the required data.

    For  Category_3 just the Column T

    For Category_4 nothing to lookup it stays N/A

    For Category_2 and Category_3 there are no blanks same is the case with Category_1
    I must have missed to fill in all the rows. 

    Category_3 Columns A to E rows are merged if the rows values are the same.

    I attempted to write the code after setting the filter based on the wsSource rows. I need to create empty rows to paste the data. I might be doing it in a lengthy manner. I better leave it to you.

    The below code is incomplete, code has yet to determine the numbers of rows based on COUNTA that was used earlier in the code.

    Sub add_rows()

    Dim wb As Workbook

    Dim ws As Worksheet

    Dim ws3 As Worksheet

    Dim LastRow As Long

    Set wb = ActiveWorkbook

    Set ws = wb.Sheets(4)

    Set ws3 = wb.Sheets(3)

    LastRow = wb.Sheets(3).Range("A" & Rows.Count).End(xlUp).Row - 3

        For r = 7 To LastRow

        ws.Cells(r, 1).EntireRow.Insert

        Next r

    End Sub

    Was this answer helpful?

    0 comments
  5. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-03-28T23:52:01+00:00

    OK. Is this a completely separate report and the particular "Categories" worksheet should not be included in the Unique Number reports?

    If it is a a separate report then we should not copy the categories template to the Unique Number reports.  Plus if we are to create a completely separate workbook report then we need a workbook naming convention (eg. "Categories") but please confirm how you wish to name.

    There is no need to insert rows for each of the categories. I am thinking that for Category 1 we simply copy the "Category 1" column headers and the data goes under it for whatever number of rows are required.

    Then we leave a row and copy the Category 2 headers and repeat for whatever number of rows and same for Category 3 and  Category 4.

    It is very easy to insert the borders after copying the data. We locate each category header (I can do that easily ) and then the range is the CurrentRegion and the outline border is thick and the inside borders are thin.

    Please reply and confirm if my assumptions are correct otherwise I need more explanation of what is required.

    Was this answer helpful?

    0 comments