Reordering columns shouldn't be this hard!

[Jimbob2000]

I have this macro that should reorder the columns on a report that downloads from a database. Except, it just seems to crash excel whenever I run it. I've tested the individual parts of the macro separately, and they seem to work fine, but all together... nope.

Any ideas would be gratefully received!

Sub CAC_No_Download()

'This Macro takes the MKF database referrals report and processes it ready to use on the DAC template

'

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Gives the active worksheet a nice name
Dim dwn As Worksheet
Set dwn = ActiveSheet

'Formats the cells, unmerges, unhides, and deletes the top 7 rows because they're sorta junk
dwn.Columns.EntireColumn.Hidden = False
dwn.Rows.EntireRow.Hidden = False
dwn.Cells.UnMerge
dwn.Rows("1:7").Delete
dwn.Range("A:Z").ColumnWidth = 15
dwn.Rows.RowHeight = 12.75

'Reformats some of the columns
With dwn.Range("N:N")
    .NumberFormat = "General"
    .Value = .Value
End With

With dwn.Range("Q:Q")
    .NumberFormat = "General"
    .Value = .Value
End With

'Renames the locator info column
dwn.Range("I1").Value = "Locator Info"

'Now we put the columns in the right order...
'Locator Info
dwn.Range("I:I").Copy
dwn.Range("A:A").Insert

'ZIP
dwn.Range("O:O").Copy
dwn.Range("A:A").Insert

'First Name
dwn.Range("E:E").Copy
dwn.Range("A:A").Insert

'Last Name
dwn.Range("E:E").Copy
dwn.Range("A:A").Insert

'Referral Date
dwn.Range("H:H").Copy
dwn.Range("A:A").Insert

'Custom Case ID
dwn.Range("V:V").Copy
dwn.Range("A:A").Insert

'This clears the contents of the other columns
With dwn.Range("G:AZ")
    .ClearContents
    .ClearFormats
End With

'Next we resort the data by CAC#
With dwn.Sort.SortFields
        .Clear
        .Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
End With
        
    With dwn.Sort
        .SetRange Range("A:G")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'And now we apply a formula to extract the locator number from column F...
dwn.Range("G2:G10000").FormulaR1C1 = _
        "=IFERROR(LEFT(RC[-1],FIND(""*"",RC[-1])-1),""0"")"
        
'And give column G a nice header
dwn.Range("G1").Value = "Locator"

'This turns the screen updating back on
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Application.CutCopyMode = False

'And this selects and copies what we've done ready to paste it into the DAC template
Dim LastRow As Long
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Range("A2:G" & LastRow).Copy

Range("H1").Value = ":-)"

End Sub

 

[Joe4]

Have you tried stepping through the code line-by-line to see exactly where things seem to go bad?

 

[Jimbob2000]

Have you tried stepping through the code line-by-line to see exactly where things seem to go bad?

Yes... weirdly, when I do that, it works just fine. It seems like trying to do everything at once overloads the macro or something.

 

[alansidman]

Have you looked at doing this with Power Query? Post a sample worksheet with XL2BB and a mocked up solution to see how that works for you.

 

[Jimbob2000]

Have you looked at doing this with Power Query? Post a sample worksheet with XL2BB and a mocked up solution to see how that works for you.

I haven't ... I'm not familiar with queries... I've done a bit of investigating, and it seems to be a useful option. It doesn't seem as automatic as doing it with VBA, though. At least, I don't know how to automate it.

 

[Jimbob2000]

I think I figured out what was going wrong... I was using the clipboard a ton in the macro (.copy and .insert) and that was using up too much resource for my poor little computer so I inserted some blank columns and used the .value = .value method to make the new columns equal the columns I wanted, in the right order, and without using the clipboard.

Thanks for all your help and suggestions though! I'm going to take a look at queries because they seem pretty useful!

 

[alansidman]

To learn more about PQ and its ability to update files, look at acquiring "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. Available in the MrE store or Amazon.