problem clear header when using currentregion and need merging for two columns

[Maklil]

I have this code

Option Explicit
Sub CopyRangeFromSetFolder()

Dim desWS As Worksheet, wb As Workbook, lRow As Long
Dim wbNm As String, Fld As String

Application.ScreenUpdating = False

Set desWS = ThisWorkbook.Sheets("Sheet1")

desWS.Range("A2").CurrentRegion.ClearContents

Fld = ThisWorkbook.Path & "\"
    wbNm = Dir(Fld & "*.xls*", vbNormal)

    Do While wbNm <> ""
        If wbNm <> ThisWorkbook.Name Then
        
            With GetObject(Fld & wbNm)
                With .Sheets("MATCH")
                    lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    .Range("A2:E" & lRow).Copy
                    
                    desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
                End With
                .Close False
            End With
        End If

        wbNm = Dir()
    Loop

Application.ScreenUpdating = True
End Sub

I need two thing:
1- fixing problem clear the headers when using this line

desWS.Range("A2").CurrentRegion.ClearContents

should clear from row 2 not the headers
2- I need add procedure to merging numirc values in columns D,E based on column B (it means there are many dupliactes items in columnB should merging with summing values for column D,E.

 

[Joe4]

1- fixing problem clear the headers when using this line

desWS.Range("A2").CurrentRegion.ClearContents

should clear from row 2 not the headers

Try this:

desWS.Range("A2").CurrentRegion.Offset(1, 0).ClearContents

2- I need add procedure to merging numirc values in columns D,E based on column B (it means there are many dupliactes items in columnB should merging with summing values for column D,E.

I am not sure if I can help with this. Please show us a sample of what your data currently looks like, and what it needs to look like after this is done.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Maklil]

thanks Joe for fixing first request
as to second :


[

FILE DATA1.xlsx
	A	B	C	D	E
1	S.N	BATCH	BRANDS	RETURNS	QTY
2	1	BT-FD-100	B-DREF12	0	88
3	2	BT-FD-101	B-DREF13	0	900
4	3	BT-FD-102	B-DREF14	0	348
5	4	BT-FD-103	B-DREF12	0	1149
6	5	BT-FD-104	B-DREF16	0	20
7	6	BT-FD-105	B-DREF17	0	5
8	7	BT-FD-106	B-DREF18	0	13
9	8	BT-FD-107	B-DREF13	1270	140
10	9	BT-FD-108	B-DREF20	0	4
11	10	BT-FD-109	B-DREF21	1984	864
12	11	BT-FD-110	B-DREF22	200	0
13	12	BT-FD-111	B-DREF23	0	16
14	13	BT-FD-112	B-DREF24	1983	596
MATCH

FILE DATA2.xlsx
	A	B	C	D	E
1	S.N	BATCH	BRANDS	RETURNS	QTY
2	1	BT-FD-107	B-DREF13	560	7
3	2	BT-FD-108	B-DREF20		22
4	3	BT-FD-109	B-DREF21	123	12
5	4	BT-FD-110	B-DREF22	333	0
6	5	BT-FD-111	B-DREF23	333	16
7	6	BT-FD-105	B-DREF17	331	5
8	7	BT-FD-106	B-DREF18	0	13
9	8	BT-FD-107	B-DREF13	111	140
MATCH

after pull data from two files from sheet MATCH should merge duplicates items like this

OUTPUT.xlsm
	A	B	C	D	E
1	S.N	BATCH	BRANDS	RETURNS	QTY
2	1	BT-FD-100	B-DREF12	0	88
3	2	BT-FD-101	B-DREF13	0	900
4	3	BT-FD-102	B-DREF14	0	348
5	4	BT-FD-103	B-DREF12	0	1149
6	5	BT-FD-104	B-DREF16	0	20
7	6	BT-FD-105	B-DREF17	331	336
8	7	BT-FD-106	B-DREF18	0	13
9	8	BT-FD-107	B-DREF13	1941	2081
10	9	BT-FD-108	B-DREF20	0	22
11	10	BT-FD-109	B-DREF21	2107	2119
12	11	BT-FD-110	B-DREF22	533	533
13	12	BT-FD-111	B-DREF23	333	349
14	13	BT-FD-112	B-DREF24	1983	596
MATCH

 

[Joe4]

So, please explain this process in more detail.

1. Are you dealing with multiple files (workbooks) or multiple pages (worksheets) within the same file?

2. If you are dealing with multiple files (workbooks), are you bringing all the data into one file?
How are you doing that? Are you copy/pasting the data all into one file/location?
Where does the final output go?

3. In trying to reconcile your example, I am not sure if there was some mistake, some data was left off, or I am not understanding the logic.
When looking at BT-FD-107, I get a total of 1941 for Returns (which matches what you show), but only 287 for quantity (where you show 2081).
Can you explain how you got to 2081 in this example?

 

[Maklil]

Are you dealing with multiple files (workbooks) or multiple pages (worksheets) within the same file?

I'm dealing with multiple files (workbooks) for the same (worksheet) is MATCH within the same file where bring data

2. If you are dealing with multiple files (workbooks), are you bringing all the data into one file?

yes

How are you doing that? Are you copy/pasting the data all into one file/location?

yes

When looking at BT-FD-107, I get a total of 1941 for Returns (which matches what you show), but only 287 for quantity (where you show 2081).

yes you're right
doesn't seem merging data correctly for the code I use it , sorry!!
should be
[

OUTPUT.xlsm
	A	B	C	D	E
1	S.N	BATCH	BRANDS	RETURNS	QTY
2	1	BT-FD-100	B-DREF12	0	88
3	2	BT-FD-101	B-DREF13	0	900
4	3	BT-FD-102	B-DREF14	0	348
5	4	BT-FD-103	B-DREF12	0	1149
6	5	BT-FD-104	B-DREF16	0	20
7	6	BT-FD-105	B-DREF17	331	10
8	7	BT-FD-106	B-DREF18	0	26
9	8	BT-FD-107	B-DREF13	1941	287
10	9	BT-FD-108	B-DREF20	0	26
11	10	BT-FD-109	B-DREF21	2107	876
12	11	BT-FD-110	B-DREF22	533	0
13	12	BT-FD-111	B-DREF23	333	32
14	13	BT-FD-112	B-DREF24	1983	596
MATCH

 

[Joe4]

I am still not clear on where all the data resides and the process.
So, are you saying that you have 3 different files:
1. File1
2. File2
3. Output
and you are bringing the data from File1 and File2 into this Output file?
If so, are you bringing them into new tabs (worksheets)?

And is the Output tab already set up with all the column A, B, and C values, and you just need to populate columns D and E with formulas?

 

[Maklil]

and you are bringing the data from File1 and File2 into this Output file?

yes

are you bringing them into new tabs (worksheets)?

the Output tab already set up

And is the Output tab already set up with all the column A, B, and C values, and you just need to populate columns D and E with formulas?

no !
the orginal data in file OUTPUT before bring data contains sheet name SHEET1 and the headers in row1 from A:E there are no data . it will brings data from the others files start row2 from A: E
all of the files are in same folder when run the file OUTPUT will search for sheet MATCH for all of the files , then will brings within file OUTPUT and into sheet1 .

 

[Joe4]

the orginal data in file OUTPUT before bring data contains sheet name SHEET1 and the headers in row1 from A:E there are no data . it will brings data from the others files start row2 from A: E
all of the files are in same folder when run the file OUTPUT will search for sheet MATCH for all of the files , then will brings within file OUTPUT and into sheet1 .

Still not really clear to me here...

So, in this workbook, you have an Output tab, and then other tabs with data copied in from other files. Is that correct?
Are there only two other tabs (for two files), or might there be more than two?
How are these tabs named?

I am a bit confused also regarding what you are saying the Output tab.
So it sounds like an Output tab already exists.
If so, what is in that tab at the beginning? Is it just headers, or headers and SOME data?

Quite frankly, this whole thing sounds like a relational database to me, where something like Microsoft Access or Power Query would be easier to work with (that is precisely the kind of thing they were designed for!).

 

[Maklil]

So, in this workbook, you have an Output tab, and then other tabs with data copied in from other files. Is that correct?

yes

Are there only two other tabs (for two files), or might there be more than two?

might there be more than two.

How are these tabs named?

I don't understand but each file contains multiple tabs (MAIN, DATA,MATCH ....)
taht's why the code specify sheet name MATCH .

what is in that tab at the beginning? Is it just headers, or headers and SOME data?

just headers.

look to understand the code . all of files are existed in the same folder
two files contain multiple tabs , what's the important is sheet name MATCH contains data from A1: E contains data , so when run the macro within file OUTPUT doesn't contain data except the headers will search in the folder any file contains sheet name MATCH , then will brings all of data from all of files , what I look for add procedure of end the code to merge duplcates items within file OUPUT and sheet1 as I posted in last picture . that's it

 

[Joe4]

OK, I think I see now. Here is how I think I would approach it:
Since all the data is being posted to the same output tab (each files' data below the previous), ) I think I would add two additional temporary columns, for calculating total RETURNS and QTY.
Then create SUMIFS formulas for each row, which totals up the values for the value in column B for the whole range (and do this for both RETURNS and QTY).
Then do a Copy/Paste Special Values, copying those values over from these temporary columns over top of the values in columns D and E, and then get rid of the two temporary columns we created.
Lastly, use Excel's built-in "Remove Duplicates" functionality to remove all the duplicate rows so you are only left with one for each value in column B.

You can get a lot of the VBA code snippets needed for these pieces using the Macro Recorder.
So give it a try, and see how you do, and post back here if you get stuck and need help with a certain part of it.