VBA to format a table data dump with column headers and dates

Cowboy11

New Member
Joined
Feb 18, 2012
Messages
15
Hi all,

I'm working on a project to format raw data that has been loaded to an Excel spreadsheet from a SQL query.

I would like to automate formatting the data with the headers for each column and the start date.
I have found some information on inserting the date, but nothing on using the column headers for each subsection.
I have attached 3 images
1. Raw data extract
2. As Is
3. Perfect world report

I was thinking to call code by adding a message box to format the report or not--> Yes would format --> No would not
After Yes or No is selected then have a Print report message box
Any help or tips on what I'm trying to accomplish, would be greatly appreciated.

This is the code I'm currently working with.
I have uploaded a image called As Is to show the results
Currently the top row doesn't get date and cell merge for 11/16/2020 not sure why and I really have no idea on how to insert the headers for each sub section.

I used help to develop this from someone else's post on another Site

VBA Code:
Sub Test2()
Dim LastE As Range, ThisE As Range, InsRow As Range
Dim i As Long
Dim StartDate As Date, LastDate As Date
   Dim StopCounter As Integer

   Const Steps = 9

   'Find last filled cell in column E
Set LastE = Range("E" & Rows.Count).End(xlUp)
'Find the date to start
StartDate = Date + Steps
For Each ThisE In Range("E2", LastE)
If ThisE.Value2 >= StartDate Then
Set LastE = ThisE
Exit For
End If
   Next

   'Get the last date
LastDate = LastE.Value
'From bottom to top
For i = LastE.Row - 1 To 5 Step -1
'Refer to the cell in column E
Set ThisE = Range("E" & i)
'Different date?
If ThisE.Value <> LastDate Then
'Remember this date for the next round
LastDate = ThisE.Value
'Insert a row below
ThisE.Offset(1).EntireRow.Insert
'Refer to the inserted row
Set InsRow = ThisE.Offset(1).EntireRow
'Refer to this row
With InsRow
'Do some formattings
.RowHeight = 24.75
'Note:
' The dot in front of Range means it refers to InsRow
' So "A1" refers incremental from the top left cell of InsRow
With .Range("E1")
With .Font
.Bold = True
.Name = "Arial"
.Size = 18

End With


.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
.HorizontalAlignment = xlRight
'Get the date from the row below
.Value = ThisE.Offset(2).Value
End With
.Range("A1:K1").Merge
With .Range("A1:K1")
With .Borders
.LineStyle = xlContinuous
.Weight = xlMedium
End With
End With

End With

'Break?
StopCounter = StopCounter + 1
If StopCounter = Steps Then Exit For
End If
Next
 End Sub



Thanks
 

Attachments

  • Report-After.png
    Report-After.png
    102.8 KB · Views: 5
  • Report-Before.png
    Report-Before.png
    85.8 KB · Views: 5
  • Report-As Is.png
    Report-As Is.png
    98.9 KB · Views: 5

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

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