VBA Challenge to prepare worksheet

[cizzett]

So I have a file I export daily and currently I have my workbook perfectly but I have the columns of the export file in the specified order to work with my script when I pull the data from the exported file.

What I am wondering is if it is possible to select the columns in the data file that will include a lot of extra columns, remove all the columns I don't need and reorder the columns in a specific order based on the headers in row 1?

The export data can be exported in any number or column orders and I want my workbook to be easier for others to use without having to reorder their columns before exporting.

The columns in order would be:
Storage Location, Item Number, Lot Number, Load Number, Manufactured Date, Expiration Date, Received Date, Unit Quantity, Description.

 

[oldbrewer]

at some stage do you IMPORT data ?

 

[cizzett]

Yes I actually currently run a VBA that deletes and imports the data in two sets into my workbook but I wanted to try to avoid have a Fixed export order from the database to the file in excel, which I am then getting this data from.

Here is the flow:

Export from database => into Donor Excel worksheet => Delete columns, remove duplicates, do a text to columns => copy column set 1 to workbook => copy column set 2 to workbook => close and do not save the donor file

I just need to get the columns mentioned in the original post to be in order as posted above A:I so I can run everything else and achieve this so if the column order isn't correct when exported from the database it doesn't provide wrong data.

 

[oldbrewer]

Surely the 2 columns can arrive into a holding sheet in any order then some vba can order them or place is certain columns before the final move to where you want them...

 

[cizzett]

I got her all figured out, at least its working now.....

Sub columnOrder()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim df As Workbook


Dim ws As Worksheet: Set ws = wb.Sheets("Today")
Dim ds As Worksheet


'  This section removed duplicates


    WaitingMsg.Show


    Application.ScreenUpdating = False


    Set df = Workbooks.Open(ws.Range("Q1").Value)
    Set ds = df.ActiveSheet


Dim search As Range
Dim cnt As Integer
Dim colOrdr As Variant
Dim indx As Integer


ds.Activate


colOrdr = Array("Storage Location", "Item Number", "Lot Number", "Inventory Status", "Load Number", "Manufactured Date", "Expiration Date", "Received Date", "Unit Quantity", "Description") 'define column order with header names here


cnt = 1




For indx = LBound(colOrdr) To UBound(colOrdr)
    Set search = Rows("1:1").Find(colOrdr(indx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not search Is Nothing Then
        If search.Column <> cnt Then
            search.EntireColumn.Cut
            Columns(cnt).Insert Shift:=xlToRight
            Application.CutCopyMode = False
        End If
    cnt = cnt + 1
    End If
Next indx


df.Close SaveChanges:=True
    Application.DisplayAlerts = False
    ws.Range("D3").Select
    Unload WaitingMsg


End Sub