Hi everyone,<\/p>\n
I am asking for help since I have got stuck with my code and barely can find solution on my own even after I tried to make a Google search on my topic.<\/p>\n
I have a huge data set which starts from A:AW and many columns down. In my work, I have to create several new tables (in new worksheets) based on user-defined-criterias. This working task I perform on a quite offen basis as soon as I receive the update on the sorting data set (the number of columns and criteria-column are always the same).<\/p>\n
VBA task: I made a code which take a given data set into input-array and depending on user-defined-criteria finds the match and copy all rows to the right.<\/p>\n
My personal issue:
\n1. I would like to pik up the concrete columns in a specific sequences: 19, 20, 18, 31, 28,41.
\n2. I would like my output-table to be re-sized once more so that in my new worksheet the table will start from A1:.
\nHow can I modify my VBA-macro to reach the desired result?<\/p>\n
Sub ComplianceTabel()
\nDim lRow As Long
\nDim lCol As Long
\nDim lCount As Long
\nDim rInputTable As Range
\nDim rTarget As Range
\nDim arInput()
\nDim arOutput()
\nDim vPattern As Variant<\/p>\n
On Error GoTo ErrorHandle<\/p>\n
vPattern = InputBox(“Angiv complience gruppe”, “Identifikator”)
\nIf Len(vPattern) = 0 Then Exit Sub<\/p>\n
Set rInputTable = Range(“A1”).CurrentRegion
\narInput = rInputTable.Value
\nSet rInputTable = Nothing
\nReDim arOutput(1 To UBound(arInput), 1 To UBound(arInput, 2))<\/p>\n
For lRow = 1 To UBound(arInput)
\nIf arInput(lRow, 22) Like vPattern Then
\nlCount = lCount + 1
\nFor lCol = 23 To UBound(arInput, 2)
\n‘the output table should have colon-numbers in the next sequence: 19, 20, 18, 31, 28,41
\narOutput(lCount, lCol) = arInput(lRow, lCol)
\nNext
\nEnd If
\nNext<\/p>\n
If lCount = 0 Then
\nMsgBox “Ingen r\u00e6kker opfyldte s\u00f8gekriteriet.”
\nGoTo BeforeExit
\nEnd If<\/p>\n
Worksheets.Add
\nSet rTarget = Range(“A1″).Resize(UBound(arOutput), UBound(arOutput, 2))
\nrTarget.Value = arOutput<\/p>\n
BeforeExit:
\nOn Error Resume Next
\nSet rTarget = Nothing
\nErase arInput
\nErase arOutput<\/p>\n
Exit Sub
\nErrorHandle:
\nMsgBox Err.Description & ” Procedure CopyRows”
\nResume BeforeExit
\nEnd Sub<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"inline_featured_image":false,"iawp_total_views":9},"question-category":[53],"question_tags":[],"class_list":["post-9312","question","type-question","status-publish","hentry","question-category-office"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question\/9312","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question"}],"about":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/types\/question"}],"author":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/comments?post=9312"}],"wp:attachment":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/media?parent=9312"}],"wp:term":[{"taxonomy":"question-category","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question-category?post=9312"},{"taxonomy":"question_tags","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question_tags?post=9312"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}