Help with extracting certain data to another sheet.

[jedibrown]

Hi, I need some help with a spreadsheet I am currently working on.

Basically, I am creating this for M.I purposes.

I have got 1 sheet with raw data and used the Sumproduct formula to extract the amounts into a table in another sheet. The table has the current status' down the side, the age along the top (7 days of less, 8-14 days, 15-28 days and 28 days+) and then in the table is the amount of items which correspond.

I am now looking to be able to click on each amount and it take you to the raw data for those items.

For example one status is "Client Feedback received". For 7 days or less there are 16 items. I woud like to be able to click on something that will then take me to JUST those 16 items. I know how to do buttons to sheets etc but I can't figure out how to extract just those 16 items to another sheet.

I hope this makes sense - Can anyone help please?

Thanks

 

[baitmaster]

welcome to the board

this is theoretically possible, but no small task, involving various elements of VBA coding. Excel is not really designed for this type of thing, and I am sure there are other pieces of software more suited to this, built around SQL. The actual complexity of your requirement, depends on the complexity of the Sumproduct, and the number of different formulas you want this to work with

For a method I would try, you would need the following:

1. An autofilter applied to your data, preferably an Excel 07 data table
2. One or more search boxes created for use with this, which align with all of your sumproduct formulas and the columns being used by them
3. A Worksheet [selection change] event for your results sheet, which looks for the name of the cell that you have just clicked, and applies the search settings needed, which then drive the autofilter

An example of a search facility that I use is found at this link and this link, your requirement would build on this feature, and the filter settings would be applied by the macro described at point 3, controlling the search terms described in 2

 

[jedibrown]

Thanks for the reply.

Could I not do this separately? So say we forget the table I have done with sumproduct, pretend I haven't created one and I just want to copy certain information from one sheet to another depending on a certain cell value?

So say if Column b from my raw data (sheet1) says "Client Feedback Received", it copies the whole row to another sheet (sheet 2)?

Although I would need to to have more than 1 criteria.

I would need - Status (Client feedback received) and between 2 dates.

For instance:

Column B - status
Column G - Date logged

I would need it to copy it if the status was "client feedback received" and the date logged was 7 days ago, 8-14 days ago, 14-28 days ago or 28+ days ago.

The ages would be on seperate sheets. So if column B says "client feedback received" and it was logged under 7 days ago, it would go to sheet 1 but if it was logged 8-14 days ago, it would go to sheet 2 etc etc.

Thanks

 

[baitmaster]

As a general rule, you can do most things with Excel, if you know how. So yes, you could do this separately

If you have a dataset, you could extract data from it in various ways. There are two main things you need - the criteria for saying whether an item is extracted or not, and a trigger to start the process

In my first response, I am assuming your criteria will vary depending on which SUMPRODUCT you have clicked on, and hence we need to look at all eventualities, depending how complex each of your formulas is, and which ones you want to use. I have used [you clicking the cell] as the event that triggers the process, however the cell might not be a sumproduct formula, so we need to handle that as well, this can quickly get quite complex; I am using the autofilter approach as it handles multiple criteria quite neatly, which is what your sumproduct is likely to use

In your revised requirement, you are looking at simply extracting to several different sheets, depending on just 2 criteria. This is also achievable, but I would probably use a different method, simply using VBA to write the data to the correct location. I imagine this would only need to be run occasionally, and hence a button to start the process would probably suffice

So the question becomes, what are you actually trying to achieve? What actual process do you want to follow, e.g.:

1. hit the button to start the process
2. clear previous results
3. look at age field and feedback field, and copy all results to one of 8 specific sheets depending on these two criteria
4. browse to each sheet using the sheet tabs whenever required

This is a different requirement to how I interpreted it before, and is less complex. Either are achievable, so you need to define the process you are trying to create

Hope this helps

 

[jedibrown]

Sorry - I think I was over complicating things!

Each day I will need to go into this workbook and paste new raw data. The previous days I will store in another workbook to feed through to charts etc.

From the raw data, I would like to be able to hit a button which will paste each row into a different sheet depending on the status (column b) and the date logged (column G).


For instance, if column B says "Client Feedback Recieved" and Column G says a date under 7 days from today, I would like all of the rows that meet that criteria to go into sheet 3. If Column B says "awaiting sign off" and Column G says a date under 7 days, I will need those rows in sheet 3.

This will need to be done on a daily basis.

Ideally I would like to link this is into the data table, only so that if you click the amount, it takes you to the relevant page.

I hope this makes more sense!

This is the data table for your information - Hopefully it will help make sense of it.

<TABLE style="WIDTH: 453pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=603 border=0 x:str><COLGROUP><COL style="WIDTH: 182pt; mso-width-source: userset; mso-width-alt: 8850" width=242><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 182pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=242 height=17></TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 70pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=93>7 days or less</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 75pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=100>8-14 days</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 74pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=99>15 - 28 days</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=69>28 days +</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Client Feedback Received</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>16</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>2</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Awaiting Client Sign Off</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>4</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>13</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Awaiting F Sign Off</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>2</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>22</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Awaiting Testing</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Client - Rejected Sign Off</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Awaiting Client Info</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>2</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>31</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Client Pending</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>2</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>In Progress</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>5</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>8</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>58</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Pending</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>44</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>19</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>165</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Rejected - F Sign Off</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>1</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Rejected - Non Issue</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>14</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>10</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" x:num>273</TD></TR></TBODY></TABLE>

Thank you for all your help!

 

[baitmaster]

You'll probably need to adjust this to suit your needs, but it should provide a framework for you

Option Explicit
Option Compare Text
 
Public wsDestination As Worksheet
 
Sub exampleCopyPaste()
Dim i As Integer
Dim strThisStatus As String, lngThisDate As Long
Dim wsSource As Worksheet: Set wsSource = Sheets("sheet1")
 
For i = 2 To 20 ' example only - assumes 1 header row, and data to row 20 - change this line to suit your model
    strThisStatus = wsSource.Cells(i, 2): lngThisDate = Round(CLng(wsSource.Cells(i, 7)), 0)
 
    Select Case strThisStatus
 
        Case "Client Feedback Received"
            Select Case lngThisDate
                Case Is < CLng(Date) - 28
                    Set wsDestination = Sheets("sheet6")
                Case Is < CLng(Date) - 14
                    Set wsDestination = Sheets("sheet5")
                Case Is < CLng(Date) - 7
                    Set wsDestination = Sheets("sheet4")
                Case Is < CLng(Date)
                    Set wsDestination = Sheets("sheet3")
            End Select
 
        Case Else
            Set wsDestination = Sheets("sheet7")
            ' other actions here
 
    End Select
 
    wsSource.Rows(i).Copy
    wsDestination.Cells(nextEmptyRow(wsDestination), 1).PasteSpecial Paste:=xlPasteValues
 
Next i
 
Application.CutCopyMode = False
 
End Sub
 
 
 
Function nextEmptyRow(ws As Worksheet)
 
On Error Resume Next ' in case destination sheet is empty
nextEmptyRow = ws.Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
On Error GoTo 0 ' switches off error handling, as only desired for this error
 
nextEmptyRow = nextEmptyRow + 1 ' next empty row = row after last row used
 
End Function

Option Explicit forces you to declare your variabl;es
Option compare text means only the text is compared, not whether it is capitalised
The Select Case framework is like a multi-way IF statement
Let me know if there's anything else you want explained