VBA - Macro for making banking file quicker to work with

[JRAMZ88]

Hi everyone - First off, I want to say thank you all for being such great resources - I have received a lot of help from some of you on here!
I'm hoping someone can help me with this particular request.

I have a file with daily banking activity that I download every day.
The report has tons of "garbage" that I don't necessarily need. So I'm hoping someone can assist me with this VBA code to help me streamline and be more efficient in the workplace.

Here's what I need the macro to do:

-select entire worksheet and unmerge all cells (file comes merged from the bank)
-then, I need to delete columns A, B, C, F, G, J and K (these are all garbage fields)
-then, once the above mentioned columns are gone, I need to insert 2 new columns in between columns A and B.

• the first column, which will now be B will contain the number 40
• the second new column will now be C will contain a concatenate formula to concatenate columns A and C starting in cell C7.

-lastly, once all of this is complete, I need to filter column E by Card Type in alphabetical order... the headings are listed on row 6.


I understand this is a lot to ask for, and it makes it hard to visualize - I have included a sample screenshot with dummy data to show what the end result would be that I'm looking for.




Any help or guidance would be much appreciated!!

 

[Nine Zero]

In your second bullet point.... did you mean to concatenate A and B ?

Also in your first bullet point.... is the entire column cells going to have the value of 40?

 

[JRAMZ88]

In your second bullet point.... did you mean to concatenate A and B ?

Also in your first bullet point.... is the entire column cells going to have the value of 40?

I apologize - I meant to concatenate newly inserted columns B and C.
And yes, the entire column B will have the total 40 in each cell all the way down to wherever the data ends.

The reasoning for this is because the invoice numbers get cut off when I download the file for some reason .. all invoice numbers start with "40" so I figured it would be best to concatenate them.

For column B, the 40 will start in cell B7 all the way down until the end of the data.
For column C, the concatenate formula will start in cell C7 all the way down until end of the data as well.


Thank you Nine Zero for the help!!

 

[JRAMZ88]

I apologize - I confused myself!

It will be to concatenate columns A and B - you were correct!! The concatenate formula will start in column C7.

 

[Nine Zero]

Alright,

Unfortunately I will not have enough time to help with the rest but this will do everything up until the filtering of column E

Try it out and let me know if it works

Sub Bank()
    Cells.Select
    Selection.UnMerge
    Range("A:A,B:B,C:C,F:F,G:G,J:J,K:K").Select
    Range("K1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    LastRow& = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "40"
    Range("B7").Select
    Selection.AutoFill Destination:=Range("B7:B" & LastRow), Type:=xlFillDefault
    Range("B7:B" & LastRow).Select
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],RC[-2])"
    Range("C7").Select
    Selection.AutoFill Destination:=Range("C7:C" & LastRow), Type:=xlFillDefault
    Range("C7:C" & LastRow).Select
End Sub

 

[JRAMZ88]

No worries - please know I am very appreciative of you taking the time out of your day to assist

I tried out the code - it did just about everything but I received an error code - Run-time Error '9': Subscript out of Ranger - Debug shows error here --> LastRow& = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

It's weird because I got this message for August 1st bank transactions, but not August 2nd... for August 2nd, the 40 populated correctly and the concatenate formula worked flawlessly.

 

[JRAMZ88]

Ah ok I think I figured out the error - so the VBA code shows the worksheet labeled as Sheet1 but that won't be the case in this scenario.

Every day I download the banking activity and move it into a workbook - I label each worksheet as month.date followed by dollar amount - example 08.01 - 100.00, 08.02 - 1250.00, 08.02 - 525.25, etc.
So each worksheet will be a different day's worth of activity and dollar amount.

Is there any way to adjust this VBA code to fit this request?

If so, this will have solved my problem and save me a lot of valuable time!!!!

Thank you!

 

[Nine Zero]

Yeah so what you can do is remove the sheet part since i imagine you will only run the macro when you are in the current sheet..... try this

LastRow& = Cells(Rows.Count, 1).End(xlUp).Row

 

[pvr928]

Hi JRAMZ88

May I suggest this looks like a task more suitably addressed by Power Query. Available from Excel 2010 (as an add-in for 2010 and 2013 and built-in with 2016), this can be a very easy and intuitive tool to use to achieve tasks similar to what you are describing.

The beauty of it is once you set it up, it can be repeated at infinitum. It can also point to folders to process files contained within.

Although similar results can be achieved in VBA, the advantage PQ has is maintainability; ie learning PQ is infinitely easier than VBA (I see a lot of what I do in VBA being replaced by PQ).

Search the web for Power Query resources to get started. www.powerquery.training , www.powerpivotpro.com , Advanced Excel Training | Advanced Excel Courses Online are good places to start.

Cheers

pvr928

 

[JRAMZ88]

Yeah so what you can do is remove the sheet part since i imagine you will only run the macro when you are in the current sheet..... try this

LastRow& = Cells(Rows.Count, 1).End(xlUp).Row

Hi Nine Zero - this has fixed my error! I'm extremely grateful for individuals like yourself who take the time out of their day to assist with requests like these.

May I ask one final question?

The last piece of this vba is to sort the card types in alphabetical order (i.e. American Express, Visa, Mastercard, etc).
The heading name "Card Type" sits in cell E6... so everything below this in this column needs to be sorted in alpha.

Is this possible? This would be the last step I'd need!