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:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top