Filtering Data and Moving it to a New Column

[jrincar]

I realize this is a bit of a tricky question so I am going to attempt to break it down piece by piece,

I have been working on a tool to automate the formatting of our monthly budget revisions into our report style.

The macro essentially copies a large spreadsheet (A-Z, with about 500 rows but they can vary) to a blank page where I then hide certain columns and filter different values out with auto filter.

I will attach a copy of the code I currently have in case it is relevant/ there is anything I can improve on to make it more efficient.

Please Note: The code must be as versatile\universal as possible seeing as the formatting doesn't always match up.

Private Sub CommandButton1_Click()
'Turn off screen updating to improve efficiency
Application.ScreenUpdating = False

'Copy Enitre "Progress Claim" Chart to Active Worksheet
Sheets("Progress Claim").Range("A1:T500").Copy Range("A1")

'Change Cell Background Colors to White
ActiveSheet.Range("$A$1:$T$500").Interior.Color = -4142

'Change Font To Match Report
ActiveSheet.Range("$A$1:$AA$500").Font.Name = "Calibri Light"
ActiveSheet.Range("$A$4:$AA$500").Font.Size = "9"
ActiveSheet.Range("A3:AA3").Font.Size = "10"
ActiveSheet.Range("A3:AA3").Font.FontStyle = "Bold"

'Change Font Color to match Report Style
ActiveSheet.Range("$A$4:$T$500").Font.Color = RGB(64, 64, 70)
ActiveSheet.Range("A3:AA3").Interior.Color = RGB(77, 115, 138)
ActiveSheet.Range("A3:AA3").Font.Color = -4142

'Change Row Height and Column Width Format
Rows("3:1000").RowHeight = 18
Columns("A").ColumnWidth = 5
Columns("B").ColumnWidth = 22
Columns("T").ColumnWidth = 15
Columns("AA").ColumnWidth = 25

'Remove cell outlines and Align Column A to Left
ActiveSheet.Range("A1:Z1000").Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
ActiveSheet.Range("A1:A1000").HorizontalAlignment = xlLeft
ActiveSheet.Columns("AA").HorizontalAlignment = xlRight

'Format T Column as Currency
ActiveSheet.Columns("T").NumberFormat = "#,##0.00_);[Red](#,##0.00)_)"

'Hide Unused Columns
ActiveSheet.Columns("D:S").Hidden = True
ActiveSheet.Columns("U:Z").Hidden = True

'AutoFilter Blank cells from Column A and 0 Values from Column T
With ActiveSheet
    .AutoFilterMode = False
    .Range("A3:T500").AutoFilter
    .Range("A3:T500").AutoFilter field:=1, VisibleDropDown:=False, Criteria1:="<>"
    .Range("A3:T500").AutoFilter field:=2, VisibleDropDown:=False
    .Range("A3:T500").AutoFilter field:=3, VisibleDropDown:=False
    .Range("A3:T500").AutoFilter field:=20, VisibleDropDown:=False, Criteria1:="<>0"
End With

ActiveSheet.Range("A3").Value = "Code"
ActiveSheet.Range("B3").Value = ""
ActiveSheet.Range("C3").Value = "$"
ActiveSheet.Range("T3").Value = ""
ActiveSheet.Range("AA3").Value = "Notes"

Columns("C").ColumnWidth = 1
Range("C5:C500").Value = "$"

'Turn screen updating back on to show output
Application.ScreenUpdating = True

End Sub

The Code above gives me this output. (See Picture Below)


-- removed inline image ---


Here is my problem. On the original spreadsheet that we are copying from the "Progress Claim" sheet, column B is populated with the titles for the "Broad Categories" in all UPPERCASE followed by the names of the materials that make up the "Broad Categories", we'll call them our "Supplies", in proper case. The list pictured above shows only the "Broad Categories" and supplies with changes to the budget and the exact amount of the change.

I need to remove or hide the entire spreadsheet after the last value in column b with an all UPPERCASE value. I then need to take those values that have just been removed(or hidden), find out which one represents the most significant change for the broad category (highest or lowest) and move the name of that supply to the "Notes" column beside the broad category it represents. All the code numbers for the supplies follow a specific pattern for example the supplies related to the Broad Category General Requirements would only have supplies listed with a code number between 1000-2000.

I can elaborate more if needed, I am mostly interested in if this is even theoretically possible.

Thanks in advance for the help!

 

[jrincar]

-- removed inline image ---

 

[jrincar]

 

[SpillerBD]

What version of Excel is your company using?

 

[jrincar]

This is so annoying, someone please tell me how the f--k to upload a picture cause nothing is working! I cant even edit my post now

 

[jrincar]

2016

 

[jrincar]

 

[SpillerBD]

You have to use the add-in the grab Excel shots. All other files should be shared in DropBox/OneDrive or similar and a link provided.
See Posting Aids at http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

With 2016, use the Get and Tranform tools to change and filter the data you want to keep. That "loads" the data into a table where you would then run a macro IF the Table formatting tools are insufficient for your needs OR if the companies existing Report design would be compatible with the Pivot Table formatting have the report go there.

For Pivot Table Formats, I have a macro that recreates a desired format for me rather than chase down a format that works. That can also be done for Table Formats (that formatting can be shared for consistency between Pivot Tables and Tables.)

With that many hidden columns (I hate hidden columns even more than hidden rows) that's a nightmare to expand, or maybe they should be omitted from that report though still retained.

 

[jrincar]

All I need to do is copy and paste it to a word document after I achieve the desired output. I am not worried about hidden columns and not interested in starting over again just because you do it a different way. Someone might as well just delete the post.

 

[SpillerBD]

All I need to do is copy and paste it to a word document after I achieve the desired output. I am not worried about hidden columns and not interested in starting over again just because you do it a different way. Someone might as well just delete the post.

Since you're copying to Word in the end, yeah it doesn't matter how you get there.
I think the question that is key is "How do you determine if text in a cell is all upper case?"
I think in VBA environment case matters where on the Excel sheet it doesn't when comparing. So using UCase to compare the original value should give true when the original is UpperCase already.