CONSOLIDATED MI REPORT

[MoonLove]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.