VBA or Calculation to interrogate and copy data into different worksheet

[VonHelson]

Hi,

I'm new to in depth Excel calculations and I can't seem to figure out how to do this.

I've got a spreadsheet with over 4000 rows of data regarding financial application details. This data is then split over 13 columns. I need to be able to automate a report that interrogates the data in Cell D and pulls out all the row data of cases that meet the criteria "KFIComplete."

I've tried using Pivot Tables with a slicer but the generated report formatting is completely wrong and I've tried a VBA button program but all this did was flick between the sheets at high speed before crashing the spreadsheet.

Here's what I've got -

Account Number

Customers Names

TotalApplicants

StatusNameId

Broker Name

MaxLoanAmount

InitialLoanAmountRequested

CustomerRequestedFacility

LoanToValue

VariantCode

ProductCategory

ProductName

ApplicationDate

111​

John Smith

1​

KFIComplete

Tim Nelson

360000​

10000​

600000​

15%​

N/A

OPEN

1​

16/09/2019​

222​

Kieran Walker

1​

Complete

Tim Nelson

385000​

23000​

700000​

17%​

N/A

OPEN

2​

14/10/2019​

333​

Tina Hughes

1​

Inprogress

Tim Nelson

482000​

48000​

920000​

23%​

N/A

OPEN

6​

20/05/2019​

444​

Judd Apatow

1​

KFIComplete

Tim Nelson

156000​

23600​

1580000​

23%​

N/A

OPEN

3​

08/09/2020​

555​

Craig Howe

1​

Declined

Tim Nelson

224000​

90000​

1480000​

29%​

N/A

OPEN

5​

30/09/2019​

666​

Linda Marting

1​

KFIComplete

Tim Nelson

102000​

20000​

25600​

42%​

N/A

OPEN

2​

09/07/2020​

777​

Justine Smith

1​

Inprogress

Tim Nelson

985000​

198000​

489000​

12%​

N/A

OPEN

1​

15/03/2020​

888​

Howard Bilson

1​

Complete

Tim Nelson

420000​

42000​

458888​

5%​

N/A

OPEN

6​

15/05/2019​

999​

Shirly Trapp

1​

Complete

Tim Nelson

690000​

12000​

15680​

2.80%​

N/A

OPEN

3​

03/07/2019​

And here's what I need to see in a separate worksheet -

111​

John Smith

1​

KFIComplete

Tim Nelson

360000​

10000​

600000​

15%​

N/A

OPEN

1​

16/09/2019​

444​

Judd Apatow

1​

KFIComplete

Tim Nelson

156000​

23600​

1580000​

23%​

N/A

OPEN

3​

08/09/2020​

666​

Linda Marting

1​

KFIComplete

Tim Nelson

102000​

20000​

25600​

42%​

N/A

OPEN

2​

09/07/2020​

The report needs to be updatable so that when I add new account data to the Raw Data, it'll continue to split out the cases at KFIComplete stage.

This is the VBA I've tried that crashed my worksheet -

Private Sub CommandButton1_Click()

a = Worksheets("Raw Data Sheet").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

If Worksheets("Raw Data Sheet").Cells(i, 4).Value = "KFIComplete" Then
Worksheets("Raw Data Sheet").Rows(i).Copy

Worksheets("KFI Data Only").Activate
b = Worksheets("KFI Data Only").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("KFI Data Only").Cells(a + 3, 1).Select
ActiveSheet.Paste
Worksheets("Raw Data Sheet").Activate

End If

Next

Application.CutCopyMode = True
ThisWorkbook.Worksheets("Raw Data Sheet").Cells(1, 1).Select


End Sub

Can anyone give me a hand to understand what I'm doing wrong?

Thanks!

 

[denzo36]

Hello, what version of Excel do you have?

 

[VonHelson]

Hello, what version of Excel do you have?

Office 2010

 

[denzo36]

What you're essentially doing is filtering the StatusNameID column by "KFIComplete" and copying and pasting that to a new sheet. This will do this for you. You could add a shape or whatever to your spreadsheet and assign the macro to it.

Sub CopyandFilter()

    Worksheets("Sheet1").Activate
    Range("A1").AutoFilter Field:=4, Criteria1:="KFIComplete"
    Selection.CurrentRegion.Select
    Selection.Copy
    Sheets.Add 'you may not need this step if you already have a sheet you want to paste the data to
    ActiveSheet.Paste
    Columns.AutoFit
    Rows("1:1").Font.Bold = True
    Worksheets("Sheet1").Activate
    Sheet1.ShowAllData
    Range("A1").Select
  
End Sub

You just need to careful with your sheet names - the sheet with your list may not be called 'Sheet 1' so you will need to edit that as appropriate - if you are renaming the sheet where the data is being pasted, you will also need to insert a step that selects that sheet first before pasting rather than insert a new sheet

 

[denzo36]

Sorry just saw that your sheets have names so you would need to replace "Sheet1" in the code to "Raw Data Sheet" and select the sheet called "KFI Data Only" to paste the information to

 

[VonHelson]

Sorry just saw that your sheets have names so you would need to replace "Sheet1" in the code to "Raw Data Sheet" and select the sheet called "KFI Data Only" to paste the information to

Ok, this got me half the way there, it auto sorted and selected copy but it didn't auto copy the information into the KFI Data Only tab. Is this possible? and is it possible that the code will auto add new data?

For example, I complete a month end data dump consisting of a further 100 cases. Within those cases are 23 sat at KFIComplete, will this code recognise the new values and sort them across or will I need to run the script during every data dump?

To be honest, this level of automation is already pretty good.

Thanks for your help.

 

[denzo36]

this change should copy the data into your KFI Data Only tab

Sub CopyandFilter()

    Worksheets("Raw Data Sheet").Activate
    Range("A1").AutoFilter Field:=4, Criteria1:="KFIComplete"
    Selection.CurrentRegion.Select
    Selection.Copy
    Worksheets("KFI Data Only").Activate
    ActiveSheet.Paste
    Columns.AutoFit
    Rows("1:1").Font.Bold = True
    Worksheets("Raw Data Sheet").Activate
    Sheet1.ShowAllData
    Range("A1").Select
    
End Sub

If you are adding new data to the Raw Data Sheet then just delete all the data from the KFI Data Sheet and run the macro again and it will copy the new data

 

[VonHelson]

this change should copy the data into your KFI Data Only tab

Sub CopyandFilter()

    Worksheets("Raw Data Sheet").Activate
    Range("A1").AutoFilter Field:=4, Criteria1:="KFIComplete"
    Selection.CurrentRegion.Select
    Selection.Copy
    Worksheets("KFI Data Only").Activate
    ActiveSheet.Paste
    Columns.AutoFit
    Rows("1:1").Font.Bold = True
    Worksheets("Raw Data Sheet").Activate
    Sheet1.ShowAllData
    Range("A1").Select
   
End Sub

If you are adding new data to the Raw Data Sheet then just delete all the data from the KFI Data Sheet and run the macro again and it will copy the new data

Denzo, you're amazing, that did the trick.

 

[denzo36]

No problem