Excel VBA Filter Results to different Worksheetd

ok

too much info here but we need to dissect it and go 1 step at a time.
I read your post .. several times ... and too much info not clear.

Give me a resume in English on what you are aiming to achieve if I understand what you want then will worry about the technical part after.


waiting to hear from you
gowflow

Hey Gowflow,

From T+A Extract everything that has a Y in the load column should be loaded into FG Upload
Everything with a Y in Ammendment loaded into the Adjustments
Everything with a Y in 'More than 40...' in the Errors.

Hope this helps.

Thanks

ok lets take the first one:
From T+A Extract everything that has a Y in the load column should be loaded into FG Upload

1) If I look at T_A Extract Load is the Col AB Correct ?
2) You say Everything that has a Y: what do you mean by Everything all columns ? or Columns V to AE like in your post ?
3) should be loaded into 'FG Upload' First you don't have a sheet called 'FG UPLOAD' you have 'FG TIME UPLOAD' is this the one your referring to ?? then where to copy this data ? Presume under row 14 if this is the case then I can't see the fields matching ... as those in T+A Extract are not the same as in 'FG TIME UPLOAD'


Just a small comment if you expect an Expert to help you then:
1) you got to be clear
2) Concise
3) Know what you want and explain well
4) Avoid confusions.

I am ususally patient and if you agree to follow the above then I am willing to help.

gowflow

Hi Gowflow,

Sorry, let me try and explain a little better -

From T+A Extract I want to copy the following columns to FG Time Upload, loading into Fg Time Upload from row 15 onwards, and only where Column AB - Load (Y/N) in T+A Extract = Y

T+A Extract - Fieldglass JobseekerID => FG Time Upload - Job-Seeker_Id
T+A Extract - LastName => FG Time Upload - Last_Name
T+A Extract - FirstName => FG Time Upload - First_Name
T+A Extract - Week Start Date => FG Time Upload - Date
T+A Extract - Week Start Date => FG Time Upload - Date
T+A Extract - Cost Centre Code => FG Time Upload - Cost_Centre_Code
T+A Extract - Cost Centre Name => FG Time Upload - Cost_Centre_Name
T+A Extract - Hours Type => FG Time Upload - Hours Type
T+A Extract - Hours Type => FG Time Upload - Hours Type
T+A Extract - Total Hours => FG Time Upload - Total Hours
T+A Extract - Cost Centre Name => FG Time Upload - [c] Cost Centre

I would like to do this through a VBA macro.

If you are able to help get me started with the VBA macro, I can then build the VBA for the other two worksheets.

Thank you

ok by the time you replied I had figure it out by reading your post over and over .... anyway don't worry ur insafe hands after the job is done if you like programing then this code will be a good start on good basis for you. Sorry not meant to be mean but I know what I deliver.

One question that you surely haven't thought of 'Maybe', when we do the transfer of data all items as per you description will go in the respective sheets. My questions are:

1. What happens with the Initital data in T+A EXTRACT ? it stays there or deleted ?
2. Next time we need to transfer new data, presume you want the new records to go after the last one in their respective sheets right ??

PLs advise so I take into considerations all issues.
gowflow

Hi Mate,

Sorry some times I struggle explaining technical requests :)

The team will use this macro weekly with new data extracts imported into and overwriting the T+A Extract & Active Headcount worksheets, so in answer to your questions -
1. Lets leave the source data just for validation purposes
2. No the team will use a fresh macro each week to create the time upload files.

HTH

Thank you

ok 1 more

Will it ever happen that you could get a Y in more than 1 of the following fields on the same row ?
Ammendment (Y/N)
More than 40 hrs std (Y/N)
Load (Y/N)

I only noticed 1 exception in row 14 of T+A EXTRACT as there is a Y in both Ammendment (Y/N) and More than 40 hrs std (Y/N) is this normal or simply a mistake ??? Like the Y only in 1 situation ? its like a process I guess.

gowflow

Hi Mate,

Ammendment & More than 40 hrs would be possible, in theory Load should only ever get a Y if the others are N

Thanks

ok again 1 more

Would you mind If I use the last 3 columns in T+A EXTRACT as DateStamp I would need AF and AG and AH if you forsee more fields then I can push more to the right I have no problem this is just to avoid duplication as you can never predict the user usage as if this is not there it would be piling duplicate data in the other sheets each and every time the macro is run.

I will be stepping out of office and will catch you back in an hour or so.
The macro is all done and tested only need your confirmation on the date stamp columns to code it accordingly.

gowflow

Sounds great to me - Thank you

So you did not answer my question:
Can I use AF, AG and AH or you need them for other fields.
gowflow

Yes, please feel free - Thank you

ok here is your solution:

1) I have modified your Mapping file and set it up so you put in Col A the sheet Name, Col B the Field in that sheet and in Col C the Field at the destination sheet. This Macro is Dynamic ie you can create as many fields as you like as long as their punctuation is exactly as they are in the sheets the macro will work and data will be transferred to the correct fields.

2) You will see a Dashboard sheet this is where we setup the buttons to run the macros with a small explanation to the user.
Basically 2 macros:
Transfer Data: Will Transfer the fields in Mappings as per your requirement to the desired sheets as outlined in the description. When Data is transferred there is a time stamp for every record in its corresponding column to avoid that record being transferred again on a future run of the macro creating duplication of records.

Clear Data: Basically will clear all data from the 3 sheets ADJUSTMENTS, ERRORS, and FG TIME UPLOAD from row 15 downward and will also clear the timestamps in Col AF to AH allowing for possibly a new run of the macro in case data was manually changed after a certain run or preparing for new data.

Please play with it and mostly check if all is fine, try adding new fields in Mapping I see you have yellows put the sheet for these and the destination field and run the macro and see if the fields get updated.

Let me know if you have any question.

For your reference here is the code that you can find in the attached workbook.

Option Explicit

Sub TransferData()
Dim WSTA As Worksheet
Dim WS As Worksheet
Dim WSMap As Worksheet
Dim MaxRowTA As Long, MaxColTA As Long, MaxRowMap As Long, MaxRow As Long, I As Long, J As Long, K As Long
Dim RowTA As Long
Dim vSheets As Variant, vFields As Variant, vDates As Variant

Dim cCell As Range, Rng As Range, cFieldTA As Range, cFieldDest As Range
Dim lCount As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WSTA = Sheets("T+A EXTRACT")
If WSTA.AutoFilterMode = True Then WSTA.AutoFilter.ShowAllData
MaxRowTA = WSTA.Range("A" & WSTA.Rows.Count).End(xlUp).Row
MaxColTA = WSTA.Columns(WSTA.Columns.Count).End(xlToLeft).Column

Set WSMap = Sheets("Mapping")
If WSMap.AutoFilterMode = True Then WSMap.AutoFilter.ShowAllData

'---> Filter Mapping on sheet T+A EXTRACT
WSMap.UsedRange.AutoFilter Field:=1, Criteria1:=WSTA.Name
MaxRowMap = WSMap.Range("A" & WSMap.Rows.Count).End(xlUp).Row

vSheets = Array("ADJUSTMENTS", "ERRORS", "FG TIME UPLOAD")
vFields = Array("Ammendment (Y/N)", "More than 40 hrs Std (Y/N)", "Load (Y/N)")
vDates = Array("AF", "AG", "AH")

'---> Start Transfer
For I = LBound(vFields) To UBound(vFields)
    
    '---> Set the Worksheet Destination
    Set WS = Sheets(vSheets(I))
    MaxRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row + 1
    Set cCell = WSTA.Range("1:1").Find(what:=vFields(I), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    
    If Not cCell Is Nothing Then
        
        '---> Filter Data based on Criteria "Y"
        WSTA.AutoFilterMode = False
        WSTA.Range(WSTA.Cells(1, "A"), WSTA.Cells(MaxRowTA, MaxColTA)).AutoFilter Field:=cCell.Column, Criteria1:="Y"
        WSTA.Range(WSTA.Cells(1, "A"), WSTA.Cells(MaxRowTA, MaxColTA)).AutoFilter Field:=WSTA.Columns(CStr(vDates(I))).Column, Criteria1:=""
        On Error Resume Next
        Set Rng = WSTA.Range(WSTA.Cells(2, "A"), WSTA.Cells(MaxRowTA, MaxColTA)).SpecialCells(xlCellTypeVisible).EntireRow
        If Not Rng Is Nothing Then
            On Error GoTo 0
        
            '---> Loop thru all the Items Filtered
            For Each cCell In Rng
                '---> Record the Row
                RowTA = cCell.Row
                
                '---> Loop Thru all the Mapping Fields
                For J = 2 To MaxRowMap
                    If WSMap.Range("B" & J).EntireRow.Hidden <> True Then
                        Set cFieldTA = WSTA.Range("1:1").Find(what:=WSMap.Range("B" & J), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                        If Not cFieldTA Is Nothing Then
                            Set cFieldDest = WS.Range("14:14").Find(what:=WSMap.Range("C" & J), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
                            
                            '---> Update Data
                            WS.Cells(MaxRow, cFieldDest.Column) = WSTA.Cells(RowTA, cFieldTA.Column)
                        End If
                    End If
                    
                Next J
                
                '---> Flag the Item as being Transfered not to be picked-up on next run
                WSTA.Cells(RowTA, CStr(vDates(I))) = Format(Now, "Mmm dd, yyyy hh:mm")
                
                '---> Increment Row in WS
                MaxRow = MaxRow + 1
                lCount = lCount + 1
            
            Next cCell
        End If
        On Error GoTo 0
        Set Rng = Nothing
        
    End If
    Set cCell = Nothing
    
    '---> Release Autofilters
    If WSTA.AutoFilterMode = True Then WSTA.AutoFilterMode = False
    WS.UsedRange.EntireColumn.AutoFit
Next I

'---> Release Autofilters
If WSMap.AutoFilterMode = True Then WSMap.AutoFilterMode = False
    
'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

If lCount = 0 Then
    MsgBox "No Records where elligible to be transfered.", vbInformation, "Transfer Data"
Else
    MsgBox "There was " & lCount & " Records that were transfered to their coresponding worksheets successfully.", vbInformation, "Transfer Data"
End If

End Sub

Sub ClearData()
Dim WSTA As Worksheet
Dim WS As Worksheet
Dim vSheets As Variant
Dim I As Long, MaxRow As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WSTA = Sheets("T+A EXTRACT")
MaxRow = WSTA.Range("A" & WSTA.Rows.Count).End(xlUp).Row
vSheets = Array("ADJUSTMENTS", "ERRORS", "FG TIME UPLOAD")

'---> Clear Data
For I = LBound(vSheets) To UBound(vSheets)
    Set WS = Sheets(vSheets(I))
    WS.Range("15:" & WS.Rows.Count).EntireRow.Delete
Next I

WSTA.Range("AF2:AH" & MaxRow).ClearContents

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

MsgBox "Data has been cleared.", vbInformation, "Clear Data"

End Sub

Select allOpen in new window

gowflow
T-A-Macro--2017-04-19-V01.xlsm

Wow GoFlow, thank you, that looks impressive :) Would you mind doing me one small favour please, could you edit the message above, and change the file name, I need to remove the company name :)

Thank you

Company name ???? where what field what sheet ?
Please be specific !!! you think we are in your shoes and know what you know. It is not the case.
gowflow

I just noticed you edited the question and changed the file name. This you can do any time coz you asked the question. However when a post is done we can edit it as long as it has not been answered, once it is then not possible to edit. You need to ask moderator to do this. Just press the button on this question 'Request Attention' and ask for the file in thread ID: 42099568 to be removed the name of the Company.

I hv done so on the copy here and will attach it for your reference.

I would like to hear feedback on the macro and your testing.
Gowflow
T-A-Macro--2017-04-19-V01.xlsm

Looks great - I've just sent it to the team for testing, -I'll keep you posted - thanks for all your help :)

Hi Mate,

Sorry, one last change, in the T+A Extract worksheet, if the column More than 40hrs Std (Y/N) = Y, this should only go in the Error worksheet, i.e. if Ammendment (Y/N) = Y and More than 40 Hrs Std = Y = it only goes in Error, not both, also my formula in column AE isn't working - any thoughts.

Thank you :)

ok for the formula here it is:
FirstName:

=TRIM(LEFT(B2,FIND(" ",B2,1)))

Select allOpen in new window

Put this formula in AD2 and drag down.

LastName:

=TRIM(MID(B2,FIND(" ",B2,1),LEN(B2)-LEN(AD2)))

Select allOpen in new window

Put this formula in AE2 and drag down.


As for the change of code, do not want to sound picky but this will need a change of logic as we go 1 by one straight forward and now your asking to combine 2 options in certain cases.

Prefer this is dealt in a separate question order not loose readers. and to keep threads concise.

gowflow

Perfect - Thank you