VBA Challenge to prepare worksheet

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
121
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.
 
Last edited:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
121
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.
 
Last edited:

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
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

Board Regular
Joined
Jan 10, 2019
Messages
121
I got her all figured out, at least its working now.....

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,205
Messages
5,527,399
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top