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



## Maklil (Dec 16, 2022)

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 (Dec 16, 2022)

Maklil said:


> 1- fixing problem clear the headers when using this line
> 
> ```
> desWS.Range("A2").CurrentRegion.ClearContents
> ...


Try this:

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



Maklil said:


> 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 (Dec 16, 2022)

thanks  Joe  for  fixing first  request
as to  second :


[FILE DATA1.xlsxABCDE1S.NBATCHBRANDSRETURNSQTY21BT-FD-100B-DREF1208832BT-FD-101B-DREF13090043BT-FD-102B-DREF14034854BT-FD-103B-DREF120114965BT-FD-104B-DREF1602076BT-FD-105B-DREF170587BT-FD-106B-DREF1801398BT-FD-107B-DREF131270140109BT-FD-108B-DREF20041110BT-FD-109B-DREF2119848641211BT-FD-110B-DREF2220001312BT-FD-111B-DREF230161413BT-FD-112B-DREF241983596MATCH


FILE DATA2.xlsxABCDE1S.NBATCHBRANDSRETURNSQTY21BT-FD-107B-DREF13560732BT-FD-108B-DREF202243BT-FD-109B-DREF211231254BT-FD-110B-DREF22333065BT-FD-111B-DREF233331676BT-FD-105B-DREF17331587BT-FD-106B-DREF1801398BT-FD-107B-DREF13111140MATCH

after pull data  from  two  files from sheet MATCH    should  merge  duplicates items  like  this
OUTPUT.xlsmABCDE1S.NBATCHBRANDSRETURNSQTY21BT-FD-100B-DREF1208832BT-FD-101B-DREF13090043BT-FD-102B-DREF14034854BT-FD-103B-DREF120114965BT-FD-104B-DREF1602076BT-FD-105B-DREF1733133687BT-FD-106B-DREF1801398BT-FD-107B-DREF1319412081109BT-FD-108B-DREF200221110BT-FD-109B-DREF21210721191211BT-FD-110B-DREF225335331312BT-FD-111B-DREF233333491413BT-FD-112B-DREF241983596MATCH


----------



## Joe4 (Dec 16, 2022)

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 (Dec 16, 2022)

> 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.xlsmABCDE1S.NBATCHBRANDSRETURNSQTY21BT-FD-100B-DREF1208832BT-FD-101B-DREF13090043BT-FD-102B-DREF14034854BT-FD-103B-DREF120114965BT-FD-104B-DREF1602076BT-FD-105B-DREF173311087BT-FD-106B-DREF1802698BT-FD-107B-DREF131941287109BT-FD-108B-DREF200261110BT-FD-109B-DREF2121078761211BT-FD-110B-DREF2253301312BT-FD-111B-DREF23333321413BT-FD-112B-DREF241983596MATCH


----------



## Joe4 (Dec 16, 2022)

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 (Dec 16, 2022)

> 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 (Dec 16, 2022)

Maklil said:


> 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 (Dec 16, 2022)

> 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 (Dec 16, 2022)

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.


----------



## Maklil (Dec 16, 2022)

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.


----------



## Maklil (Dec 17, 2022)

> You can get a lot of the VBA code snippets needed for these pieces using the Macro Recorder.


actually I  have not  ever  used Macro Recorder , in reality  I  used   ready  procedures from  the  internet . so I'm afraid  my  way  is  wrong  when  using Macro Recorder .
I will try  based  on  your  guiding .


----------



## Joe4 (Dec 17, 2022)

Maklil said:


> actually I  have not  ever  used Macro Recorder , in reality  I  used   ready  procedures from  the  internet . so I'm afraid  my  way  is  wrong  when  using Macro Recorder .
> I will try  based  on  your  guiding .


You just turn on the Macro Recorder, and record yourself performing the actions manually, then stop the recorder.
Then, view the code you just recorded.

This is very useful in getting code snippets to cut and paste into your code.
Sometimes you need to edit them slightly to make them more dynamic.
It is is great way to help you get started in writing VBA code,

Give it a shot, see how far you get, and post back here when you need help.


----------

