Jimbob2000
New Member
- Joined
- Jun 27, 2019
- Messages
- 25
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!
Any ideas would be gratefully received!
VBA Code:
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