Export Change - Macro Possibility

[MorganK]

Good morning,

At the non profit organization I work for, we use Blackbaud for fundraising purposes and we use Daxko for our membership software. We are able to get an export from Blackbaud that we can use to import data to Daxko, but Daxko requires a certain setup with columns and data which the Blackbaud export does not do.

Here's what the Blackbaud export looks like:

Depending on the date range and the activity per date, there could be less or more items.

The Daxko import file needs to be:

In the above, the following needs to happen, and I'm hoping a macro might be able to do it:

1. The header rows get removed. This would be rows 1-10.
2. On the bottom, there's a "Grand Total" row, which needs to be removed.
3. The dates need to be duplicated for each item under that date.
4. The single date rows and the subtotal rows need to be removed.
5. The column order needs to get changed.

Here's the dropbox link to the actual spreadsheet: https://www.dropbox.com/s/a86whx9rxid4r0z/BB Export - Test.xlsx?dl=0

On the first tab, that's the original version.

On the second tab, that's the needed format.

Any help would be appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub MorganK()
   Dim Fnd As Range
   
   Set Fnd = Range("A:A").Find("Date", , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then Exit Sub
   Rows("1:" & Fnd.Row).Delete
   With Range("A1", Range("B" & Rows.count).End(xlUp).Offset(, -1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
      .Offset(, 1).Replace "*total*", "", xlWhole, , False, , False, False
      .Offset(, 1).SpecialCells(xlBlanks).EntireRow.Delete
   End With
   Range("E:E").Cut
   Range("A:A").Insert xlToRight
   Range("A:B").Insert xlRight
   ActiveSheet.UsedRange.Columns.AutoFit
End Sub

 

[MorganK]

Hi Fluff,

Thank you very much! This worked out perfectly!

 

[Fluff]

You're welcome & thanks for the feedback