# CONSOLIDATED MI REPORT



## MoonLove (Dec 31, 2022)

Hi team, Iam a new joiner and I have a very urgent project to deliver.

I have 4 workbooks with the names ( *Workbook 1,2,3,4*). With each workbook, there are 3 common worksheets named *sales, channels & products*. . I managed to get VBA macro here to pull data that are in workbooks 1, 2, 3, & 4 and consolidate them into another workbook called *"CONSOLIDATED MI REPORT*" that have the same *worksheet name* as to those four workbooks I mentioned earlier. 

My problem now is that, whenever I run the macro, data are pulled starting from the old top rows up to the new rows leading to duplications of information's. I want a macro that will only pull updated row data from workbook 1,2,3, & 4 then transfer the same to my *consolidate MI *report sheets respectively.

Please assist.


----------



## Micron (Dec 31, 2022)

By definition, "updated" data would mean that if I change A5 cell from 25 to 30 you need to pull that change in. I'd say the only way to achieve that is to wipe the target sheet clean and re-populate it from all the rows in the source.
If you mean new rows that were not copied in a prior operation, IMO you need a column with a flag (anything that id's the row has having been copied already). Then your current modified code would find the last row with the flag value (e.g row 50) _and_ the last row with data that you intend to copy (e.g. row 100). You'd then copy from 50+1 to 100.


----------



## MoonLove (Dec 31, 2022)

Micron said:


> By definition, "updated" data would mean that if I change A5 cell from 25 to 30 you need to pull that change in. I'd say the only way to achieve that is to wipe the target sheet clean and re-populate it from all the rows in the source.
> If you mean new rows that were not copied in a prior operation, IMO you need a column with a flag (anything that id's the row has having been copied already). Then your current modified code would find the last row with the flag value (e.g row 50) _and_ the last row with data that you intend to copy (e.g. row 100). You'd then copy from 50+1 to 100.


Hi Micron,

Thank you so much for you quick response:

From you response, this is basically what I mean:

"If you mean new rows that were not copied in a prior operation, IMO you need a column with a flag (anything that id's the row has having been copied already). Then your current modified code would find the last row with the flag value (e.g row 50) _and_ the last row with data that you intend to copy (e.g. row 100). You'd then copy from 50+1 to 100."

*Please find the current VBA script that Iam using and help me how to modify to achieve the above:*

Option Explicit
Dim RunMacro As Date

Sub Copy_From_All_Workbooks()
    RunMacro = Now + TimeValue("00:30:00")
    Application.OnTime RunMacro, "Copy_From_All_Workbooks"
    Dim wb As String, i As Long, sh As Worksheet
    Application.ScreenUpdating = False
    wb = Dir(ThisWorkbook.Path & "\*")
    Do Until wb = ""
        If wb <> ThisWorkbook.Name Then
            Workbooks.Open ThisWorkbook.Path & "\" & wb
                For Each sh In Workbooks(wb).Worksheets
                        sh.UsedRange.Offset(1).Copy   '<---- Assumes 1 header row
                            ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                        Application.CutCopyMode = False
                Next sh
            Workbooks(wb).Close False
        End If
        wb = Dir
    Loop
    Application.ScreenUpdating = True
End Sub
Sub stop_macro()

Application.OnTime Earliesttime:=RunMacro, Procedure:="Copy_From_All_Workbooks", Schedule:=False

End Sub


----------



## Micron (Dec 31, 2022)

This is more or less how you should post code (use code tags - vba icon on posting toolbar) to maintain indentation and readability. Given that it's New Years Eve, I may not be looking at this tonight. We'll see.

```
Option Explicit
Dim RunMacro As Date

Sub Copy_From_All_Workbooks()
Dim wb As String, i As Long, sh As Worksheet

RunMacro = Now + TimeValue("00:30:00")
Application.OnTime RunMacro, "Copy_From_All_Workbooks"
Application.ScreenUpdating = False
wb = Dir(ThisWorkbook.Path & "\*")

Do Until wb = ""
   If wb <> ThisWorkbook.Name Then
      Workbooks.Open ThisWorkbook.Path & "\" & wb
      For Each sh In Workbooks(wb).Worksheets
         sh.UsedRange.Offset(1).Copy '<---- Assumes 1 header row
         ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
         Application.CutCopyMode = False
      Next sh
      Workbooks(wb).Close False
   End If
   wb = Dir
Loop

Application.ScreenUpdating = True

End Sub

Sub stop_macro()
Application.OnTime Earliesttime:=RunMacro, Procedure:="Copy_From_All_Workbooks", Schedule:=False
End Sub
```


----------



## MoonLove (Dec 31, 2022)

Micron said:


> This is more or less how you should post code (use code tags - vba icon on posting toolbar) to maintain indentation and readability. Given that it's New Years Eve, I may not be looking at this tonight. We'll see.
> 
> ```
> Option Explicit
> ...


Happy New Year Micron ….. I really appreciate

Bear with me since Iam a new joiner😀..


----------



## Micron (Dec 31, 2022)

NP! Another tip if you like - don't quote a whole post that contains lots of code. As you can see, it just repeats a lot of unnecessary stuff.
Options:
- just type in a new post window and use "Post reply" button to post your response, or
- copy what you want to quote, click quote button on posting toolbar and paste within the resulting quote tags, or
use +Quote like you did and strip out the code and any other unwanted stuff. That might not work well if you accidentally remove tags.

Last tip - make sure you answer the question as best you can. I still have no idea what "updated row data" means to you. Copying and pasting my musings about it without addressing it doesn't help. Nor does your code provide any clue as to that definition. Nor do I know if you agree and will create the flag column, or which column it would be, or if the cell value in each row would be the same or even if that would matter (that's 4 points, not 1). So for me, there are at least 5 unknowns.

Answering a bunch of unexpected questions is the price one pays for free help. Usually the cause is that the one with the issue doesn't know the depth of a particular problem or issue and its solution. Sometimes it's about wondering if there is a better approach.
Not trying to nitpick here, just passing on tips for a newcomer to posting (if that's what you meant). It will make your posts more inviting to responders.

Happy New Year to you as well!


----------



## MoonLove (Dec 31, 2022)

Thank you for your response Micron:

I want my script to work as follows:

It should go and copy only rows that were not previously copied and transfer them to consolidated mi report sheets.
Since for each sheet in those four workbooks(1,2,3,4), the first column is a date, I would also like the code to take the row with the most current date.
For the case of the creating a column with a flag (a column that will show that data has already been copied), I am still a learner in VBA, can you please assist.


----------



## MoonLove (Jan 1, 2023)

Hi Micron,

Your assistance on the above issue please.


----------



## Micron (Jan 1, 2023)

Looks like I can't send you a PM? Maybe you don't have enough posts to get pm's. Trying to work out something today.


----------



## Micron (Jan 1, 2023)

Try the posted code below. I had to swap in/out paths and wb names since I don't have yours. As a result I only tested once. Since you didn't indicate which column would contain the flag, I chose H and chose to insert the date, which I thought would be the most useful indicator. I also assumed that column A would be the column that would suitable for choosing the the rows for which the data had not yet been copied. HTH.

```
Sub Copy_From_All_Workbooks()
Dim wb As String
Dim sh As Worksheet
Dim lngStartCopy As Long, Lrow As Long

RunMacro = Now + TimeValue("00:30:00")
Application.OnTime RunMacro, "Copy_From_All_Workbooks"
Application.ScreenUpdating = False
wb = Dir(ThisWorkbook.Path & "\*")
Do Until wb = ""
     If wb <> ThisWorkbook.Name Then
          Workbooks.Open ThisWorkbook.Path & "\" & wb
          For Each sh In Workbooks(wb).Worksheets
               lngStartCopy = sh.Cells(Rows.count, "H").End(xlUp).Row + 1 'where to start copied range
               Lrow = sh.Cells(Rows.count, "A").End(xlUp).Row 'where last row is with data in column A
               
               'if sheet is blank or flag/data is wrong, start row can be greater than end row
               If Not lngStartCopy > Lrow Then '
                    sh.Range("A" & lngStartCopy & ":A" & Lrow).EntireRow.Copy
                    ThisWorkbook.Sheets(sh.Name).Cells(Rows.count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                    Application.CutCopyMode = False
                    sh.Range("H" & lngStartCopy & ":H" & Lrow) = Date
               End If
          Next sh
          Workbooks(wb).Close False
     End If
wb = Dir
Loop

Application.ScreenUpdating = True

End Sub
```


----------



## MoonLove (Dec 31, 2022)

Hi team, Iam a new joiner and I have a very urgent project to deliver.

I have 4 workbooks with the names ( *Workbook 1,2,3,4*). With each workbook, there are 3 common worksheets named *sales, channels & products*. . I managed to get VBA macro here to pull data that are in workbooks 1, 2, 3, & 4 and consolidate them into another workbook called *"CONSOLIDATED MI REPORT*" that have the same *worksheet name* as to those four workbooks I mentioned earlier. 

My problem now is that, whenever I run the macro, data are pulled starting from the old top rows up to the new rows leading to duplications of information's. I want a macro that will only pull updated row data from workbook 1,2,3, & 4 then transfer the same to my *consolidate MI *report sheets respectively.

Please assist.


----------



## MoonLove (Jan 3, 2023)

Hi Micron,

Thank you for you support.

I ran the above above code, however It doesn't work in a way that I want. Let me clarify my request more:

- I have 4 workbooks called GK, SK, TB and RJ.
- Each of the workbook contains 3 worksheets(with the same names i.e. (*sales, channels & products* ).
- All the sheets in all workbooks, the first column with data is column  'A' with a name : *"DATE".*
- I want all the data in all 4 work books to be transferred in consolidated workbook but remember these four workbook are updated daily, so the script should be able to only pick the rows with the most current data based on (today's date).

I hope that's clarify's a lot.

Thank you for you support, I truly appreciate a lot.


----------



## Micron (Jan 3, 2023)

MoonLove said:


> It doesn't work in a way that I want.


That doesn't reveal much. I just reviewed what I posted and unfortunately I can't see how it would not do what you've outlined in your last post - as long as files are in the right places I guess.


----------



## MoonLove (Jan 4, 2023)

Hi Micron,

Thank you so much. I really appreciate.

I tried to run the above code that you have shared but it retrieve all the rows data be it new or old data.

My request is that the code should only retrieve new rows with data based on current DATE and transfer to the consolidated date.

Please see below screenshot of the workbooks column headers that I have:


----------



## Micron (Jan 4, 2023)

Around post 6 I said you need a way to flag that which has already been copied. You haven't said anything about that.


----------



## MoonLove (Jan 6, 2023)

Hi Micron,

How do I do so? I mean how do I flag that which have already been copied in column "A"?. 

Thank you for support till today.


----------



## Micron (Jan 6, 2023)

Anyway you want. The value in the rows of that column could be anything but preferably all the same. Could be a date or any letter such as x or any number. If nothing is there, the copy happens for those rows. If you later remove a value, you'll copy from that row again.


----------



## MoonLove (Saturday at 12:15 PM)

Dear Micron,

From the below code where am I supposed to amend:



```
Sub Copy_From_All_Workbooks()
Dim wb As String
Dim sh As Worksheet
Dim lngStartCopy As Long, Lrow As Long

RunMacro = Now + TimeValue("00:30:00")
Application.OnTime RunMacro, "Copy_From_All_Workbooks"
Application.ScreenUpdating = False
wb = Dir(ThisWorkbook.Path & "\*")
Do Until wb = ""
     If wb <> ThisWorkbook.Name Then
          Workbooks.Open ThisWorkbook.Path & "\" & wb
          For Each sh In Workbooks(wb).Worksheets
               lngStartCopy = sh.Cells(Rows.count, "H").End(xlUp).Row + 1 'where to start copied range
               Lrow = sh.Cells(Rows.count, "A").End(xlUp).Row 'where last row is with data in column A
              
               'if sheet is blank or flag/data is wrong, start row can be greater than end row
               If Not lngStartCopy > Lrow Then '
                    sh.Range("A" & lngStartCopy & ":A" & Lrow).EntireRow.Copy
                    ThisWorkbook.Sheets(sh.Name).Cells(Rows.count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                    Application.CutCopyMode = False
                    sh.Range("H" & lngStartCopy & ":H" & Lrow) = Date
               End If
          Next sh
          Workbooks(wb).Close False
     End If
wb = Dir
Loop

Application.ScreenUpdating = True

End Sub
```


----------



## Micron (Saturday at 12:33 PM)

Based on posts like 6, 14 ,15, 16 I thought we were talking about modifying a sheet by adding a column and putting a value into it when the row was copied. After reviewing post 13, are you saying that the range to copy is to be the current date in column A? So if this task was forgotten on Monday but was done on Tuesday, then only Tuesday data would copy? In other words, the data to copy is identified by matching the date in col A with the date that this is run? You run the risk of not being able to copy data if no one did this on a particular day?


----------



## MoonLove (Saturday at 1:43 PM)

OOH YES, Basically what I wanted is that the range to copy should based on current date in column A. However it has come into my attention that some data will not be copied if the macro is not run on the same day.

I think copying the range should be based on any rows that have not been copied from the last copied row.

Thank you for raising this up.


----------



## Micron (Saturday at 2:01 PM)

MoonLove said:


> I think copying the range should be based on any rows that have not been copied from the last copied row.


So basically, we're back to what I proposed in post 6. 

```
lngStartCopy = sh.Cells(Rows.count, "H").End(xlUp).Row + 1 'where to start copied range
```
If your flag column was M you'd change H to M. When the copy is done, the code should put the current date in that column. Next time, the copy range should be from the row after the last row that contains the date when the last copy was done to the last row with data.

If in post 13 you were saying that this flag column is or will be A then change H in the code to A and see if it works for you. Do not put your own dates in the flag column.


----------



## MoonLove (Dec 31, 2022)

Hi team, Iam a new joiner and I have a very urgent project to deliver.

I have 4 workbooks with the names ( *Workbook 1,2,3,4*). With each workbook, there are 3 common worksheets named *sales, channels & products*. . I managed to get VBA macro here to pull data that are in workbooks 1, 2, 3, & 4 and consolidate them into another workbook called *"CONSOLIDATED MI REPORT*" that have the same *worksheet name* as to those four workbooks I mentioned earlier. 

My problem now is that, whenever I run the macro, data are pulled starting from the old top rows up to the new rows leading to duplications of information's. I want a macro that will only pull updated row data from workbook 1,2,3, & 4 then transfer the same to my *consolidate MI *report sheets respectively.

Please assist.


----------



## MoonLove (Sunday at 2:37 AM)

Hi Micron,

I have run the code but it doesn't copy range data that was not previously copied from my workbooks GK, SK,RJ and TB to consolidate report workbook.

Maybe the idea of flagging a column is somehow not clear to me but I amended the code as below and run it(replacing H with A):


```
Sub Copy_From_All_Workbooks()
Dim wb As String
Dim sh As Worksheet
Dim lngStartCopy As Long, Lrow As Long

RunMacro = Now + TimeValue("00:30:00")
Application.OnTime RunMacro, "Copy_From_All_Workbooks"
Application.ScreenUpdating = False
wb = Dir(ThisWorkbook.Path & "\*")
Do Until wb = ""
     If wb <> ThisWorkbook.Name Then
          Workbooks.Open ThisWorkbook.Path & "\" & wb
          For Each sh In Workbooks(wb).Worksheets
               lngStartCopy = sh.Cells(Rows.Count, "A").End(xlUp).Row + 1 'where to start copied range
               Lrow = sh.Cells(Rows.Count, "A").End(xlUp).Row 'where last row is with data in column A
              
               'if sheet is blank or flag/data is wrong, start row can be greater than end row
               If Not lngStartCopy > Lrow Then '
                    sh.Range("A" & lngStartCopy & ":A" & Lrow).EntireRow.Copy
                    ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                    Application.CutCopyMode = False
                    sh.Range("A" & lngStartCopy & ":A" & Lrow) = Date
               End If
          Next sh
          Workbooks(wb).Close False
     End If
wb = Dir
Loop

Application.ScreenUpdating = True

End Sub
```


----------



## MoonLove (Sunday at 11:45 AM)

Hi Micron or anyone to assist please. 

The above code I run them on the *consolidate report workbook(which is a destination workbook).*

Unfortunately, it does not copy any data range which was not previously copied from the workbook GK,RJ,SK and TB to consolidated report.

Kindly please assist on this. I have a pending project to deliver.


----------



## Micron (Sunday at 12:15 PM)

Post some data that needs to be copied- no pictures.


----------



## MoonLove (Sunday at 1:31 PM)

Hi Micron,

Please see below table(this is how source workbooks appears).

All the four workbooks( GK, SK,RJ and TB )- which are source workbooks have 3 sheets (*sales, channels, products*) with each sheet having the below *columns name* shown on the below table.

Also, *CONSOLIDATED WORKBOOK* HAVE THE SAME THREE SHEET NAMES(SALES, CHANNELS, PRODUCTS) with the same column names as of those four workbooks  *GK,SK,RJ AND TB*.



Date


Lead Code


Lead Name

YTD

Sales Code

08-01-2023


102




Serengeti

2023




201


08-01-2023

103

Manyara

2023

202

08-01-2023

104

Mikumi

2023

203


----------



## Micron (Sunday at 4:46 PM)

So you would want that to be copied or not? If yes, where's the flag column that indicates it was not copied yet?


----------



## MoonLove (Sunday at 8:02 PM)

Yes, I would want that data to be copied from each workbooks GK,SK,RJ and TB to CONSOLIDATED REPORT WORKBOOK.

The idea of having a flagging column I think from my end it's still not clear. 

Instead, I would like to request the code that will copy data range from workbooks GK,SK,RJ and TB *(with worksheets sales, channels and products*) to CONSOLIDATED REPORT WORKBOOK*(with worksheets sales, channels and products)* based on the *today's date data* meaning the macro will have to be run on daily basis.

Thank you for your support Micron, I truly appreciate.


----------



## Micron (Monday at 9:41 AM)

It's really quite simple and that worries me. Here, I added a column and when the copy is done, code puts (e.g.) that days date in the rows so that next time it only copies those rows where there is no date "flag". In the example below, the first 3 rows would not copy because the code put the date of copying in those rows. The next 3 would copy today and then insert today's date. This should have been solved long ago.


DateLead CodeLead NameYTDSales Code*Date Copied*8/01/23​102​Serengeti2023​201​01/08/23​8/01/23​103​Manyara2023​202​01/08/23​8/01/23​104​Mikumi2023​203​01/08/23​8/01/23​105​Serengeti2023​201​8/02/23​106​Manyara2023​202​8/03/23​107​Mikumi2023​203​


----------



## MoonLove (Tuesday at 3:17 PM)

Hi Micron,

Where should I ran the code ? In consolidated mi report workbook or individual workbooks GK,SK,RJ and TB?.



```
Sub Copy_From_All_Workbooks()
Dim wb As String
Dim sh As Worksheet
Dim lngStartCopy As Long, Lrow As Long

RunMacro = Now + TimeValue("00:30:00")
Application.OnTime RunMacro, "Copy_From_All_Workbooks"
Application.ScreenUpdating = False
wb = Dir(ThisWorkbook.Path & "\*")
Do Until wb = ""
     If wb <> ThisWorkbook.Name Then
          Workbooks.Open ThisWorkbook.Path & "\" & wb
          For Each sh In Workbooks(wb).Worksheets
               lngStartCopy = sh.Cells(Rows.Count, "A").End(xlUp).Row + 1 'where to start copied range
               Lrow = sh.Cells(Rows.Count, "A").End(xlUp).Row 'where last row is with data in column A
              
               'if sheet is blank or flag/data is wrong, start row can be greater than end row
               If Not lngStartCopy > Lrow Then '
                    sh.Range("A" & lngStartCopy & ":A" & Lrow).EntireRow.Copy
                    ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                    Application.CutCopyMode = False
                    sh.Range("A" & lngStartCopy & ":A" & Lrow) = Date
               End If
          Next sh
          Workbooks(wb).Close False
     End If
wb = Dir
Loop

Application.ScreenUpdating = True

End Sub
```


----------



## Micron (Tuesday at 3:31 PM)

Copy to a standard module. That code contains your edits and the reason it didn't work last time is that you made two code lines use A. When I told you to do that, it was based on understanding that column A was your flag column. Turns out it was not. The first line 
lngStartCopy = sh.Cells(Rows.Count, "*A*")
has to use your *flag column*.


----------



## MoonLove (Tuesday at 5:32 PM)

Hello Micron,

I run the below macro in consolidated mi report standard module however it just pull everything from my individual workbooks GK,SK,RJ and TB.


Where Iam getting it wrong? My flag column is column F(on each individual workbook GK,SK,RJ and TB).



```
Sub Copy_From_All_Workbooks()
Dim wb As String
Dim sh As Worksheet
Dim lngStartCopy As Long, Lrow As Long

'RunMacro = Now + TimeValue("00:30:00")
'Application.OnTime RunMacro, "Copy_From_All_Workbooks"
Application.ScreenUpdating = False
wb = Dir(ThisWorkbook.Path & "\*")
Do Until wb = ""
     If wb <> ThisWorkbook.Name Then
          Workbooks.Open ThisWorkbook.Path & "\" & wb
          For Each sh In Workbooks(wb).Worksheets
               lngStartCopy = sh.Cells(Rows.Count, "F").End(xlUp).Row + 1 'where to start copied range
               Lrow = sh.Cells(Rows.Count, "A").End(xlUp).Row 'where last row is with data in column A
              
               'if sheet is blank or flag/data is wrong, start row can be greater than end row
               If Not lngStartCopy > Lrow Then '
                    sh.Range("A" & lngStartCopy & ":A" & Lrow).EntireRow.Copy
                    ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                    Application.CutCopyMode = False
                    sh.Range("F" & lngStartCopy & ":F" & Lrow) = Date
               End If
          Next sh
          Workbooks(wb).Close False
     End If
wb = Dir
Loop

Application.ScreenUpdating = True

End Sub
```


----------



## MoonLove (Dec 31, 2022)

Hi team, Iam a new joiner and I have a very urgent project to deliver.

I have 4 workbooks with the names ( *Workbook 1,2,3,4*). With each workbook, there are 3 common worksheets named *sales, channels & products*. . I managed to get VBA macro here to pull data that are in workbooks 1, 2, 3, & 4 and consolidate them into another workbook called *"CONSOLIDATED MI REPORT*" that have the same *worksheet name* as to those four workbooks I mentioned earlier. 

My problem now is that, whenever I run the macro, data are pulled starting from the old top rows up to the new rows leading to duplications of information's. I want a macro that will only pull updated row data from workbook 1,2,3, & 4 then transfer the same to my *consolidate MI *report sheets respectively.

Please assist.


----------



## MoonLove (Tuesday at 5:40 PM)

Dear Micron,

Remember I have consolidated workbook of which it pulls data from individual workbooks GK,RJ,SK and TB.

The code I run them on my consolidated report. 

Each sheet on Individual workbooks i.e GK, RJ , SK and TB has a flag column (column F).

Please assist on solution of why the code still pull all the data even those that were previously copied workbooks from GK,RK,SK and TB.


----------



## Micron (Tuesday at 6:15 PM)

I can't see what you have to work with nor can I keep this up after some 30 posts. 
Maybe if you post 2 source workbook copies and one target wb copy somewhere I can take a look.


----------



## MoonLove (Wednesday at 2:04 AM)

Dear friend Micron,

I really appreciate all our journey up to here but, I think you didnot understand my problem statement and that's why we are still here today.

Allow me elaborated it again:

- I have *4 source workbooks* called *GK, SK,RJ and TB(my source workbooks).*
- Each of the above source worksheets have three worksheets called *channels, products and sales.*
-  Each sheet in all the four workbooks(GK,SK,RJ and TB) have the same table format as below:



Date


Lead Code


Lead Name

YTD

Sales Code

08-01-2023


102


Serengeti

2023


201


08-01-2023

103

Manyara

2023

202

08-01-2023

104

Mikumi

2023

203


- My *destination workbook* is called *CONSOLIDATED MI REPORT*, it has 3 sheet with the same name as of those 4 workbooks*(channels, products and sales).*
- I ran the macro on the *CONSOLIDATED MI REPORT* standard module with the expectation that it has to pull/copy data from each sheet(channels, products, and sales) on each individual workbooks*(GK,SK,RJ and TB) *and paste it to CONSOLIDATED MI REPORT.
- My challenge is when the macro runs, it copies everything even the data that was previously copied from each sheet on individual workbooks *GK, SK, RJ, and TB.*
- I requested you assistance to have a code which just pull only those data that were not previously copied from each sheets on individual workbooks *(GK,SK,RJ ant TB).*
- You suggested the idea of FLAG COLUMN, where by from my understanding, each sheet on each *source workbook(GK,SKRJ and TB) should have a column where if data have already been copied to CONSOLIDATED MI REPORT, then it will keep a memory of the last copied row.
-* I ran the code that you have shared on my destination workbook(CONSOLIDATED MI REPORT standard module) but it just behave the same, the code pulls everything from all the four workbooks.
- My flag column is column "F", please see the below code:


```
Sub Copy_From_All_Workbooks()
Dim wb As String
Dim sh As Worksheet
Dim lngStartCopy As Long, Lrow As Long

'RunMacro = Now + TimeValue("00:30:00")
'Application.OnTime RunMacro, "Copy_From_All_Workbooks"
Application.ScreenUpdating = False
wb = Dir(ThisWorkbook.Path & "\*")
Do Until wb = ""
     If wb <> ThisWorkbook.Name Then
          Workbooks.Open ThisWorkbook.Path & "\" & wb
          For Each sh In Workbooks(wb).Worksheets
               lngStartCopy = sh.Cells(Rows.Count, "F").End(xlUp).Row + 1 'where to start copied range
               Lrow = sh.Cells(Rows.Count, "A").End(xlUp).Row 'where last row is with data in column A
              
               'if sheet is blank or flag/data is wrong, start row can be greater than end row
               If Not lngStartCopy > Lrow Then '
                    sh.Range("A" & lngStartCopy & ":A" & Lrow).EntireRow.Copy
                    ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
                    Application.CutCopyMode = False
                    sh.Range("F" & lngStartCopy & ":F" & Lrow) = Date
               End If
          Next sh
          Workbooks(wb).Close False
     End If
wb = Dir
Loop

Application.ScreenUpdating = True

End Sub
```


----------



## MoonLove (Yesterday at 2:55 AM)

Dear Micron,

Good day to you..

You did not come back to me on the above ask.


----------



## Micron (Yesterday at 10:33 AM)

I asked for files and instead you blame me for this taking so long. I can tell you that others don't think that is the case. Sorry, but I am done here and will not be following any more. Good luck.


----------



## MoonLove (Today at 2:08 AM)

Hi Micron,

I have restrictions to *install XL2BB *as per the organization policy, so I can not upload the excel file and that's why I decided to explain my issue as per above.

Indeed I really need your support.


----------

