Home/ Questions/Q 9312
Next
Answered
computing
  • 6
Asked: 2021-12-01T11:01:34+00:00 In:

Solved VBA Macro To Copy Multiple Columns In User Defined Order

  • 6

Hi everyone,

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.

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

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.

My personal issue:
1. I would like to pik up the concrete columns in a specific sequences: 19, 20, 18, 31, 28,41.
2. I would like my output-table to be re-sized once more so that in my new worksheet the table will start from A1:.
How can I modify my VBA-macro to reach the desired result?

Sub ComplianceTabel()
Dim lRow As Long
Dim lCol As Long
Dim lCount As Long
Dim rInputTable As Range
Dim rTarget As Range
Dim arInput()
Dim arOutput()
Dim vPattern As Variant

On Error GoTo ErrorHandle

vPattern = InputBox(“Angiv complience gruppe”, “Identifikator”)
If Len(vPattern) = 0 Then Exit Sub

Set rInputTable = Range(“A1”).CurrentRegion
arInput = rInputTable.Value
Set rInputTable = Nothing
ReDim arOutput(1 To UBound(arInput), 1 To UBound(arInput, 2))

For lRow = 1 To UBound(arInput)
If arInput(lRow, 22) Like vPattern Then
lCount = lCount + 1
For lCol = 23 To UBound(arInput, 2)
‘the output table should have colon-numbers in the next sequence: 19, 20, 18, 31, 28,41
arOutput(lCount, lCol) = arInput(lRow, lCol)
Next
End If
Next

If lCount = 0 Then
MsgBox “Ingen rækker opfyldte søgekriteriet.”
GoTo BeforeExit
End If

Worksheets.Add
Set rTarget = Range(“A1″).Resize(UBound(arOutput), UBound(arOutput, 2))
rTarget.Value = arOutput

BeforeExit:
On Error Resume Next
Set rTarget = Nothing
Erase arInput
Erase arOutput

Exit Sub
ErrorHandle:
MsgBox Err.Description & ” Procedure CopyRows”
Resume BeforeExit
End Sub

Share

    1 Answer

    1. Best Answer
      OK, I’d love to help, but without your actual data – or generic, non-confidential data in the same layout – It’s hard for me to follow along with what your code is trying to accomplish.

      If it’s possible for you to upload a “safe” (non-confidential, no personal data) copy of your workbook to zippyshare, then I would have much better idea of what is going on.

      The one thing I can mention right off is that this snippet is probably not what you are looking for:

      Dim myColArray As Variant
myColArray = Array(22, 19, 20, 18, 31, 28, 41)' include column 22?

For lCol = 0 To UBound(myColArray)
   rawData = Sheets(myColArray(i)).Range("A2:AW" & _
                    myColArray(i).Range("A3446").End(xlUp).Row)    ' finding data area

      Let’s break that down:

      myColArray = Array(22, 19, 20, 18, 31, 28, 41)

      defines an array that contains a series of numbers meant to reference specific Columns. 22, 19, etc.

      So far, so good.

      For lCol = 0 To UBound(myColArray)

      loops through the Array elements, such that lCol will equal 0, then, 1 then 2, until it reaches the number of elements in the array.

      Still, so far, so good.

      rawData = Sheets(myColArray(i)).Range("A2:AW" & _
                    myColArray(i).Range("A3446").End(xlUp).Row)

      This is where we run into problems You used myColArray(i). Note the “i”. You have never set “i” equal to anything, so as far as VBA is concerned, it is always going to equal 0. Therefore, myColArray(i) will always equal 22, since that is the first (or “0”) element of the myColArray array. Maybe you meant to use lCol since that is your loop variable, but I don’t think even that will help, because…

      You then use myColArray(i) in Sheets(myColArray(i)). Therefore, you are asking VBA to reference Sheet(22). In fact since i will always equal 0, you are always going to reference Sheet(22). I don’t know whether or not you actually have a Sheet(22) in your workbook, but even if you do, I’m pretty sure that that is not what you are trying to reference. I say that because you said you wanted to reference specific columns, not sheets.

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0