VBA Copy and Paste conditioned by headers

[Jumia]

Hi Guys,

I have one sheet that have 133 Columns, and I need the information for only 9 of them, is there a way of looking for the header in the sheet that have 133 Columns and Copy in another sheet only the information of the 9 columns that interest me ?

The sheet that have 133 Colums is called "BOB EXPORT"
The output sheet with the 9 colunms that interest me is called ''EXPORT CLEANED''
The 9 Columns that interest me have the following headers:
-"sku_config"
-"name"
-"model"
-"sku_supplier_config"
-"sku_supplier_simple"
-"price"
-"special_price"
-"cost"
-"tax_class"

Thanks a lot guys for your help, I really need it.

 

[Jumia]

Hi Guys,

I have one sheet that have 133 Columns, and I need the information for only 9 of them, is there a way of looking for the header in the sheet that have 133 Columns and Copy in another sheet only the information of the 9 columns that interest me ?

The sheet that have 133 Colums is called "BOB EXPORT"
The output sheet with the 9 colunms that interest me is called ''EXPORT CLEANED''
The 9 Columns that interest me have the following headers:
-"sku_config"
-"name"
-"model"
-"sku_supplier_config"
-"sku_supplier_simple"
-"price"
-"special_price"
-"cost"
-"tax_class"

Thanks a lot guys for your help, I really need it.

Do you need anymore information from my side ?
Thanks a lot

 

[circledchicken]

Hi Jumia and welcome to the forum,

Yes, can you provide more details - what row are the headers in? What does the layout look like? Is it a complete table without gaps in rows? Can you post a small sample of the input sheet and expected output sheet?

 

[Jumia]

Hey Circlechicken thanks for the welcome and answer!
On the first point I don't want the function to be fixed with the rows, today they are A,B... but tomorrow they might changed that is why I really want the function to look for the header.
You will find a Google doc link that resumes what the input and output. FYI yes in some cells you will have gaps.
https://docs.google.com/spreadsheet/ccc?key=0AsNCSv2hrDAddHJIdC1NZTNVSHgxa2VtZkxhanhteXc&usp=sharing
Thanks
Augustin

 

[Jumia]

Hey Circlechicken thanks for the welcome and answer!
On the first point I don't want the function to be fixed with the rows, today they are A,B... but tomorrow they might changed that is why I really want the function to look for the header.
You will find a Google doc link that resumes what the input and output. FYI yes in some cells you will have gaps.
https://docs.google.com/spreadsheet/ccc?key=0AsNCSv2hrDAddHJIdC1NZTNVSHgxa2VtZkxhanhteXc&usp=sharing
Thanks
Augustin

Is it ok or you need more information, please let me know what you need I really need to understand how I could make this happen.

Again thanks for your time.

 

[circledchicken]

Based on the information you've provided, perhaps try something like this:

Sub example()


Dim vArr1   As Variant
Dim vArr2   As Variant
Dim i       As Long
Dim j       As Long


vArr1 = Sheets("BOB EXPORT").Range("A1:EC1") ' input headers
vArr2 = Sheets("EXPORT CLEANED").Range("A1:I1") ' output headers
' find the matching header in the
' input sheet, then copy that column
' to the output sheet
For i = 1 To UBound(vArr2, 2)
    For j = 1 To UBound(vArr1, 2)
        If vArr1(1, j) = vArr2(1, i) Then
            Sheets("BOB EXPORT").Columns(j).Copy _
                Destination:=Sheets("EXPORT CLEANED").Columns(i)
        End If
    Next j
Next i


End Sub

I've added some comments to the code that are hopefully of some use in case you need to adjust some part of it to better suit your circumstances.

 

[FormR]

Probably not the most effecient and you should try other responses, but here is one way that appears to work.

Sub Test()

Dim vArr As Variant
Dim lRow As Long
Dim lCol As Long
Dim rRng As Range
Dim fromWS  As Worksheet
Dim toWS As Worksheet

vArr = Array("sku_config", "name", "model", "sku_supplier_config", "sku_supplier_simple", "price", "special_price", "cost", "tax_class")

Set fromWS = Sheets("BOB EXPORT")
Set toWS = Sheets("EXPORT CLEANED")

lRow = fromWS.UsedRange.Rows.Count
lCol = fromWS.UsedRange.Columns.Count

Dim iArr As Long
Dim iCol As Long

For iArr = 0 To UBound(vArr)
    For iCol = 1 To lCol
        If fromWS.Cells(1, iCol).Value = vArr(iArr) Then
            toWS.Cells(1, iArr + 1).Resize(lRow).Value = _
            fromWS.Cells(1, iCol).Resize(lRow).Value
            GoTo Continue
        End If
    Next iCol
Continue:
Next iArr

End Sub

EDIT: Posted before seeing circlechickens reply: that will be the better option!

 

[Jumia]

Probably not the most effecient and you should try other responses, but here is one way that appears to work.

Sub Test()

Dim vArr As Variant
Dim lRow As Long
Dim lCol As Long
Dim rRng As Range
Dim fromWS  As Worksheet
Dim toWS As Worksheet

vArr = Array("sku_config", "name", "model", "sku_supplier_config", "sku_supplier_simple", "price", "special_price", "cost", "tax_class")

Set fromWS = Sheets("BOB EXPORT")
Set toWS = Sheets("EXPORT CLEANED")

lRow = fromWS.UsedRange.Rows.Count
lCol = fromWS.UsedRange.Columns.Count

Dim iArr As Long
Dim iCol As Long

For iArr = 0 To UBound(vArr)
    For iCol = 1 To lCol
        If fromWS.Cells(1, iCol).Value = vArr(iArr) Then
            toWS.Cells(1, iArr + 1).Resize(lRow).Value = _
            fromWS.Cells(1, iCol).Resize(lRow).Value
            GoTo Continue
        End If
    Next iCol
Continue:
Next iArr

End Sub

EDIT: Posted before seeing circlechickens reply: that will be the better option!

It is working THANKS A LOT you really helped me a lot.