Sep 23 2019 06:34 AM - edited Sep 23 2019 06:40 AM
Hi all,
i have tried a few things without success so turning to the pros...
customer records with data that is left to right as standard. One column is a yes or no answer. I need anything with the yes answer to copy into a secondary sheet. For the life of me, I cannot get this to work
columns read left to right as follows:
date; name; Speech; Diet; Physio; OT; OT req; Client Type; Location; Comment
Speech is the column that’s needs to be copied on a yes field
HELP
Sep 23 2019 06:42 AM
What did you try which didn't work?
There are several ways to achieve this, Formulas, Advanced Filter or Macro. Which way would you prefer?
Can you upload a small sample file with the layout as your original file has and mock up the desired output manually on another sheet?
Once the Yes data is transferred to another sheet, would you like to delete it from the original sheet?
Sep 23 2019 07:32 AM
thanks- you for replying, it must stay on the first sheet as well. I tried vlookup and if formula, I keep getting error. I know it’s reasonable simple, just not quite getting it, so turn to the pros.
ENQUIRY DATE | NAME | Speech | Diet | Physio | OT | OT REQUIREMENT | CLIENT TYPE | LOCATION | SOURCE OF REFERRAL | Offer to Assist | Unable to Assist | DATE PD INPUT | ASANA TASKED | NOTES | ||||||||||||||||
28/8/19 | Phil | YES | NO | NO | NO | PRIVATE PAY | Toowoomba | SIBLING | Tried to call to offer an app after email | |||||||||||||||||||||
29/8/19 | Larkin | NO | YES | NO | NO | NDIS AGENCY | Toowoomba | NDIS | Y | |||||||||||||||||||||
30/8/19 | Caitlyn | NO | NO | YES | NO | NDIS SELF | Toowoomba | WEBSITE | Y | Also first Speech assessment 27/09/2019 | ||||||||||||||||||||
30/8/19 | William | NO | NO | NO | YES | PRIVATE PAY | Toowoomba | WEBSITE | Y | Also first Speech Assessment 25/09/2019 | ||||||||||||||||||||
23/8/19 | Richter | YES | YES | NO | NO | THERAPY (eg asd etc) | DVA | Toowoomba | WORD OF MOUTH | Needing basic OT in Beenleigh | ||||||||||||||||||||
2/8/19 | Alva | YES | NO | YES | NO | NDIS AGENCY | Chinchilla | HOME INSTEAD |
Sep 23 2019 08:57 AM
You may try something like this...
Sub CopyDataForYesSpeech()
Dim wsData As Worksheet
Dim wsOutput As Worksheet
Dim lr As Long
Application.ScreenUpdating = False
Set wsData = Worksheets("Sheet1") 'Sheet with Data
lr = wsData.Cells(Rows.Count, 3).End(xlUp).Row
On Error Resume Next
Set wsOutput = Worksheets("Speech-Yes") 'Name of Output Sheet
wsOutput.Cells.Clear
On Error GoTo 0
If wsOutput Is Nothing Then
Set wsOutput = Worksheets.Add(after:=wsData)
wsOutput.Name = "Speech-Yes" 'Name of Output Sheet
End If
wsData.AutoFilterMode = False
With wsData.Range("A1").CurrentRegion
.AutoFilter field:=3, Criteria1:="YES"
.SpecialCells(xlCellTypeVisible).Copy wsOutput.Range("A1")
wsData.Range("C2:C" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With
wsOutput.UsedRange.Columns.AutoFit
wsOutput.Select
Application.ScreenUpdating = True
End Sub
In the attached, click on the button called "Transfer Data" on Sheet1 to run the code.
The code will transfer all the data from Sheet1 to a new sheet called "Speech-Yes". You may change the name of the Data and Output Sheets as per your requirement.
Sep 26 2019 03:23 AM
Thanks so much, I have changed it slightly but cannot find the removal for deleting records. I need to keep all records with the transfer of speech records to move to the other sheet. No deletion.
You assistance is very much appreciated
Sep 26 2019 03:45 AM
Please rephrase your requirement as it is not clear.
Let me know which assumption is correct?
You could simple prepare the sample file with the following sheets to show the end result you are trying to achieve...
Sheet1 (mock intake):
This should be the sheet with the original data before the macro runs.
Sheet2 (mock intake After):
This should be the sheet which should contain the data after the macro is runs.
Sheet3 (Speech-Yes Before):
This should be the sheet with the existing data if any before the macro runs.
Sheet4 (Speech-Yes After):
This should be the sheet with the transferred data after the macro runs.
If required upload a sample workbook with the sheets as mentioned above but data should not be more than 10 rows.
Sep 26 2019 03:55 AM - edited Sep 26 2019 03:58 AM
sorry about the extra lines, I was adding data to check through. Otherwise, don't worry about my last... worked it out
It wasnt deleting it was sorting.
Thank-you so very much
Sep 26 2019 04:04 AM
You're welcome! Glad it worked as desired.
Please take a minute to accept the post with the proposed solution as a Best Response/Answer to mark your question as Solved.
Sep 26 2019 04:35 AM - edited Sep 26 2019 04:36 AM
SolutionTo mark a post as a Best Response, you will find a button called Mark as Best Response below the post and you only need to click that button.
Please refer to following image...
Sep 26 2019 04:35 AM - edited Sep 26 2019 04:36 AM
SolutionTo mark a post as a Best Response, you will find a button called Mark as Best Response below the post and you only need to click that button.
Please refer to following image...