Search and Pick the File from Folder based on Cell value, then Validate the Both Reports

[Guna13]

There is a master excel file I have. The values in column F include MFG_JAGUAR_870, DSN_LABS_335 and so on.
I have two folders, FDSS and ETB, which contain around 340+ excel files.

1. ETB Folder File format - File names will be ETB _870_MFG_JAGUAR_870.xlsx, ETB_335_DSN_LABS_335.xlsx.

2. FDSS Folder File format - File names will be 870_MFG_JAGUAR_870_30.4 FDSS Map Local_Global Account By MEP_CODE.xlsx,
335_DSN_LABS_335_30.4 FDSS Map Local_Global Account By MEP_CODE.xlsx.

Go to Folder if cell F2 value is MFG_JAGUAR_870, the macro should memorize this value.
Pick the file, copy the data, and paste it into the appropriate sheet. In the Second Folder, it is the same way

Currently, I am copying and pasting the file data into a file, then validating it. For me, it will take more than a day to complete...total 350 files.

Searching for exact cell values with file names on Google returns no results.
I am unable to find and pick the file by loop condition...

All steps and processes are attached in the macro file. I wrote almost more code. However, this is not supported.

Can anyone help me on this please. Sir............................

 

[Guna13]

@Anthony47 - Hi Sir. could you please help me this post...

 

[Anthony47]

Go to Folder if cell F2 value is MFG_JAGUAR_870, the macro should memorize this value.
Pick the file, copy the data, and paste it into the appropriate sheet. In the Second Folder, it is the same way

Let's forget that these looks like "Functional specifications" rather than a problem description...

But you need to be more clear:
-"go to folder": which folder?
-" if cell F2 value is MFG_JAGUAR_870, the macro should memorize this value": maybe it will be necessary, maybe not
-"pick the file": I guess we need to open the file that includes "MFG_JAGUAR_870"; since such a file is available both in ETB /New folder and ETB /FDSS_Report its important you explain wich path we should look in
-"copy the data": I gues in the file picked there are several worksheets and a lot of cells; so whicch "data" have you in mind?
-"paste [the copied data] it into the appropriate sheet": again you should specify which workbook, which worksheet and wich range is the appropriate area to copy
-"In the Second Folder, it is the same way": so I guess that we have to search in both the directories you mentioned, so the above clarification should be related to both the first and the second search

Try your best to describe the problem and clarify the above areas

 

[Guna13]

Hi Sir, I have completed my loop condition task based on the conversation above. My macro code seems to have skipped something while running the macro. Therefore, the "Interim Acc Validation Sheet" returns a blank result, even though the data is available in the "Interim Master Data Sheet".

Based on ETB Sheet Range("P02") Value Based, I need to update data in "Interim Acc Validation" Sheet Range("B4") from data of Interim Master Data Sheet.

In short, I'm trying to gather data from "Interim Master Data Sheet" based on "ETB Sheet" Range-"P02" Value Based On Update In "Interim Accounting Validation Sheet" in Range.B4"...

In case there is no data in the Master Data Sheet. Then it will output in this format. Kindly help................

If Data available in Interim account Master data, then Output like below

If Data Not available in Interim account Master data, then Output below screen Shot Like that

Sub Interim_Acc1()
Dim wss, wss1, wss2, wss3, wss4 As Worksheet
Set wss1 = ThisWorkbook.Sheets("Interim Acc Validation")
Set wss2 = ThisWorkbook.Sheets("Baan ETB")
Set wss4 = ThisWorkbook.Sheets("Info")
Set trg = ThisWorkbook.Sheets("Interim Master Data")

trg.AutoFilterMode = False

'Call Interim_Acc_Pull_From_Sql

Dim mep As String
wss2.Select
mep = wss2.Application.Trim(Range("P2").Value)

Set wss = ThisWorkbook.Sheets("Interim Master Data")
wss1.Activate
ActiveSheet.Unprotect password:="1234"
[A1].Select
Sheets("Interim Acc Validation").Range("A4:J100000").Clear
Rows(2).Clear

trg.Activate
trg.AutoFilterMode = False
lr = trg.Range("A" & Rows.Count).End(xlUp).Row

Dim mep_Name As String
On Error Resume Next
 
  With trg.Range("A1").CurrentRegion
        .AutoFilter Field:=1, Criteria1:=mep
        lr = trg.Range("A" & Rows.Count).End(xlUp).Row
    If lr > 2 Then
        
        trg.Range("B2:E" & lr).SpecialCells(xlCellTypeVisible).Copy wss1.Range("B4")
        .AutoFilter
    End If
    End With

On Error GoTo 0

wss.Activate
lr = wss1.Range("B" & Rows.Count).End(xlUp).Row
If lr <= 3 Then
wss.AutoFilterMode = False
'wss.Protect "1234", True, True
'wss.Visible = xlSheetVeryHidden
wss1.Activate
wss1.AutoFilterMode = False
wss1.Rows(1).ClearContents
wss1.Cells(2, 1).Activate
wss1.Range("D6") = "No INTERIM ACCOUNT"
wss1.Protect "1234", True, True

Application.CutCopyMode = False
Sheets("Info").Select
Exit Sub
Else
End If

Application.CutCopyMode = False

wss1.Activate

Sheets("Interim Acc Validation").Range("A1").Value = UCase(mep)

Cells(4, 6).Select
colrow = ThisWorkbook.Sheets("Baan ETB").Cells(1, Columns.Count).End(xlToLeft).Column

Dim i As Long
On Error Resume Next
For i = 4 To Range("B100000").End(xlUp).Row

If colrow = 11 Then

Range("F" & i).FormulaR1C1 = "=IFERROR(SUMIF('Baan ETB'!C[-4],'Interim Acc Validation'!RC[-4],'Baan ETB'!C[6]),0)"

Else
Range("F" & i).FormulaR1C1 = "=IFERROR(SUMIF('Baan ETB'!C[-4],'Interim Acc Validation'!RC[-4],'Baan ETB'!C[6]),0)"

End If

Next i
On Error GoTo 0
    Sheets("Interim Acc Validation").Select
With Range("A4:A" & Range("B" & Rows.Count).End(xlUp).Row)
.Cells(1, 1).Value = 1
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
End With
Range("G4:H" & Rows.Count).Clear
Columns("I:AA").Clear

Application.ScreenUpdating = False
Application.DisplayAlerts = False
'wss1.Activate

Range("A3").CurrentRegion.Borders.Weight = XlBorderWeight.xlThin
Sheets("Interim Acc Validation").Cells.Font.Name = "Calibri"
Sheets("Interim Acc Validation").Cells.Font.Size = 9
Range("F4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Style = "Comma"

    lr = Range("F" & Rows.Count).End(xlUp).Row
    Range("F2").Formula = "=Subtotal(9,F4:F" & lr & ")"

Dim MR1 As Range
Set MR1 = Range("F2")
If MR1.Value <> 0 Then
MR1.Interior.ColorIndex = 3
Else
MR1.Interior.ColorIndex = 10
End If

Range("F2").Style = "Comma"

Application.CutCopyMode = False

wss1.Activate
ActiveSheet.AutoFilterMode = False
Rows(3).AutoFilter
Columns("G:XFD").Locked = False
ActiveSheet.Protect password:="1234", DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowSorting:=True, AllowFiltering:=True

Call Check_Sum_Total

Sheets("Info").Select
Range("A1").Select

End Sub

 

[Guna13]

@Anthony47 - Kindly recreate my code sir.

 

[Anthony47]

Please provide usable samples of your starting data as well a description of what you need to do (getting this from from "how" you have been trying to obtain that is time-consuming and unreliable).

 

[Guna13]

Please provide usable samples of your starting data as well a description of what you need to do (getting this from from "how" you have been trying to obtain that is time-consuming and unreliable).

Is my code not giving clear information? Anyway.


In short, I'm trying to copy and paste data from "Interim Master Data Sheet" based on "ETB Sheet" Range-"P02"Value.

Those copy data Update In "Interim Accounting Validation Sheet" in Range.B4" staring row... In case if No data available in "Master data sheet" then Just Update NO Interim account.

Both Output screen shot updated in above. also Unable to attached .xlsm file here Sir.. Please help me this

 

[Anthony47]

I am willing to help, but I don't like spending time to reverse engineering a macro tha, by the way, doesn't do what the user whishes to do; that is time consuming and unreliable.

Also, recreating your data is time consuming and a useless job, as you already have this datas
See XL2BB - Excel Range to BBCode for the XL2BB addin, that would allow you to insert in the message sample ouf your worksheets.
Or use an external file sharing service for share a full demo workbook; for example Dropbox, or OneDrive, or GoogleDrive and many others, all offer a "share" option (a link to shere with us for reading and downloading the file)

In short, I'm trying to copy and paste data from "Interim Master Data Sheet" based on "ETB Sheet" Range-"P02"Value.

So you wish to filter the data in "Interim Master Data Sheet" based on the value contained in "ETB Sheet" Range-"P02"? Which column should be used for matching "ETB Sheet" Range-"P02"?

And, by the way, which Office version do you use?

 

[Guna13]

I am willing to help, but I don't like spending time to reverse engineering a macro tha, by the way, doesn't do what the user whishes to do; that is time consuming and unreliable.

Also, recreating your data is time consuming and a useless job, as you already have this datas
See XL2BB - Excel Range to BBCode for the XL2BB addin, that would allow you to insert in the message sample ouf your worksheets.
Or use an external file sharing service for share a full demo workbook; for example Dropbox, or OneDrive, or GoogleDrive and many others, all offer a "share" option (a link to shere with us for reading and downloading the file)



So you wish to filter the data in "Interim Master Data Sheet" based on the value contained in "ETB Sheet" Range-"P02"? Which column should be used for matching "ETB Sheet" Range-"P02"?

And, by the way, which Office version do you use?

So you wish to filter the data in "Interim Master Data Sheet" based on the value contained in "ETB Sheet" Range-"P02"? Which column should be used for matching "ETB Sheet" Range-"P02"?

And, by the way, which Office version do you use? - yes Sir, you understand my point. Excel 64 Bit, Is it possible to use Advance Filter option VBA code for this logic.. just thinking..

 

[Anthony47]

Any chance for getting a demo workbook with usable data?
Ok 64 bits, but I mean Office 2010, 2016, 365 and the like; some of them have embedded filter capability that could simplify the solution